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

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