I had an interesting conundrum at work the other day which I solved with helper columns outside of the pivot table to drive a (non pivot) graph that was populated with values from the pivot table plus values from the helper columns. It worked great and I was able to use slicers and filters to slice and dice the pivot table and this would then update the associated chart. However this was a slightly sloppy solution so it got me think whether it was possible to do what I needed to do fully inside the pivot table and associated chart. It is a question so simple it should be easy, right? Yet despite lots of googling and other reserach I don't seem to have been able to find any solutions at all...

The source data is one row = 1 activty. This is perhaps significant since whenever I used the native functions of a pivot table such as "average of.." I just got 1 which is not what I wanted. So what did I want? I wanted a pivot table which I could slice and dice with some filters etc and would show the sum of values in the first column of values. This was the easy bit.. Then the second column would show the average of whatever set of values that were showing in column 1. Imagine you highlight those values in a pivot table in excel, it will show you the average in the right bottom right (eg average: 234) so I wanted exactly this value showing in all rows in column 2 , that the average value was pulling from. Then in column 3 a quartile eg a lower quartile - same principle but obviously the calculation is different. Column 4 upper quartile - same principle.

Column 2 proved to be impossible to do despite trying native pivot table "average of..." option which instead always gave a result of 1 which is mathematically correct of course since the average of the range of values in the source will be 1 , calcuated measure in pivot tables, building a data model and using DAX, nope nothing worked.... it seems so simple and yet so hard to do this fully inside a pivot table. Because the pivot always looks at the source data rather than itself it tends to get hung up on the source data set rather that being able to look at another summarised column in the pivot table and do a calcuation based on that.... and yet there are functionss like "% of" which looks at the pivot table itself. Ahh, maybe I am just overthinking and overlooking something obvious?