# Thread: DAX Measure to calculate Average within a percentile range

1. ## DAX Measure to calculate Average within a percentile range

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  Reply With Quote

2. 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.   Reply With Quote

3. 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...%80%93-part-i/
http://www.powerpivotpro.com/2011/10...n-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.  Reply With Quote

4. 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.

Paul  Reply With Quote

5. 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).  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•