Dynamic Percentile Analysis Across Multiple Categories - PowerPivot / DAX

[Louis_Guionneau]

Hi everyone!

I've spent a bunch of time trying to find a solution to the following issue! It's driving me currraaazzy!

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

Could anyone provide any guidance here?

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??).

Super grateful for any help!

Thanks,

Louis

 

[Louis_Guionneau]

Dynamic Percentile Analysis Across Multiple Categories - PowerPivot / DAX

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

stackoverflow.com

I have posted this question here now in the hope that I may get a response! Future visitors should see the link for additional help if they run into the same problem.

Thanks!