DAX Measure to calculate Average within a percentile range

pajarvey

New member
Joined
Nov 4, 2011
Messages
3
Reaction score
0
Points
0
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider
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

Administrator
Staff member
Joined
Mar 13, 2011
Messages
2,520
Reaction score
5
Points
38
Location
Nanaimo, BC, Canada
Website
www.excelguru.ca
Excel Version(s)
Excel Office 365 Insider

pajarvey

New member
Joined
Nov 4, 2011
Messages
3
Reaction score
0
Points
0
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

New member
Joined
Mar 22, 2011
Messages
4
Reaction score
0
Points
0
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).
 
Top