Results 1 to 5 of 5

Thread: DAX Measure to calculate Average within a percentile range

  1. #1

    DAX Measure to calculate Average within a percentile range



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,021
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

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

    Very helpful articles, thank you!

    Paul

  5. #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).

Posting Permissions

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