PDA

View Full Version : DAX Measure to calculate Average within a percentile range



pajarvey
2011-11-04, 04:59 PM
Hi All,

I'm having trouble writing a measure to dynamically calculate the average of values, where those values fall within a percentage range. For example, I would like to calculate the average of all values within percentile range 0-50% (or the lowest half of the values).

I've used MINX to identify the approximate data value at any percentage point. I'm having trouble writing DAX measure that also calculates the average for all values that are lower than that value.

Sample data is below. I've also included the DAX measure that I'm using to calculate the highest value within the percentile range. There may be an easier way to do this. Can anyone point me in the right direction?

Sample Data:
[Category] [Value]
a 13
a 12
a 10
a 19
a 11
b 12
b 10
b 15
b 14
b 12

Desired result:
Average of bottom 50% of Category B: 11.3
Average of bottom 50% of Category A: 11

DAX Measure to generate percentiles (finds the nearest value above and below the percentile and averages them):

=(MINX(FILTER(VALUES('Table'[Column]),
CALCULATE(COUNTROWS('Table'),
'FINAL-HiBAR'[Hindex] = EARLIER('Table'[Column]) )
COUNTROWS('Table')*0.5),
'Table'[Column])+
MINX(FILTER(VALUES('Table'[Column]),
CALCULATE(COUNTROWS('Table'),
'FINAL-HiBAR'[Hindex] = EARLIER('Table'[Column]) )
(COUNTROWS('Table')*0.5) -1),
'Table'[Column]))/2

Ken Puls
2011-11-04, 07:07 PM
Hi there,

My DAX isn't nearly as good as it should be. I'm going to see if I can find someone to lend a hand here. :)

Ken Puls
2011-11-05, 04:37 AM
Okay, so I heard back from a colleague, and he tells me that this isn't easy to pull off. His suggestion was to look at the following articles.
http://www.powerpivotpro.com/2011/09/creating-accurate-percentile-measures-in-dax-%e2%80%93-part-i/
http://www.powerpivotpro.com/2011/10/creating-accurate-percentile-measures-in-dax-part-ii/

Let me know if they do/don't help. If they don't, I'll see if I can get some additional help.

pajarvey
2011-11-09, 03:48 PM
I've managed to find a work-around by using one measure to calculate the lowest and/or highest value in a percentile range, placing those results in a related table, and then using a simple AVERAGEX to find the average of all values within the range.

This isn't as dynamic as I would like, but it works.

Very helpful articles, thank you!

Paul

ruve1k
2012-01-23, 04:24 AM
If your percentile is not intended to be dynamic based on filter/slicer selections, then I would create a calculated column assigning the percentile to each row of data. Then use CALCULATE(AVERAGE(Table[Column]),Table[Percentile]<=50).