Dynamic Percentile Analysis Across Multiple Categories - PowerPivot / DAX

Question

I've spent a a lot of time trying to find a solution to the following issue but I haven't been able. There are similar threads to this issue both here and on other forums but they don't seem to be applicable. Please let me know any best demonstrated practices regarding posting on this forum that I may be going against.

I would like to be able to dynamically (and hopefully in as simple way as possible) create measures (ideally NOT via calculated columns) in power pivot to be able to carry out percentile analysis (e.g., value associated with top quartile, top quintile, third decile, etc etc) on different subsets of my data (in a pivot table). For example, I might want to create the percentile based on the yearly sales associated with a shop (although the records I have are based on monthly, or another time period).

Here is what this data could look like as an example, as well as what the results would be on this data (I did this jammily using excel). I know that there is a way to do this using calculated columns but I want to try and do it using measures (e.g., maybe using a combination of sumx, percentiles, top n??).

In case you're not able to view the picture of my data, my data is structured as such:

===============================================================================================
**Shop ID** ## **Value** ## **Metric**## **Period** (e.g., mm / yy) ## **Franchised or Co Owned** ## **Year** ## **Quarter**
===============================================================================================

1               50           etc etc please see screenshot! thank you
2               70 
3               90 

Additional explanation on data

1. Shop ID could have many entries
2. Value is the value for each metric - the record is based around having a value for each metric for each shop id for each month (or other time period)
3. Metric could be things like sales, ebitda, car count, etc etc
4. Period is typically month
5. Shop status could be "Co - Owned" or "Franchised"
6. Year and Quarter are based off the period

I want to be able to get percentile values for sales in a given period (e.g., total yearly sales for a given year, total quarterly sales etc) for whatever slicer i have going on for the current pivot table.

Super grateful for any help!

Thanks,

Louis

Answer 1

OK, I think I found an answer. Something like this formula might work:

PERCENTILEX.INC(ALLSELECTED(Facts[ID]),SUMX(ALLSELECTED(Facts[Period]),[Sum Values]),[Percentile Definition])