How to add dynamic quantile ranges to a chart which change based on date selection

[Anonymous]

Hi all - I have been asked for a request that has had my head scratching for a week. I have to build using measures only as calculated columns wont work because the calculation is dynamic.

Context
I have built some DAX measures which calculates the quantiles for a set of data for YTD purchases for suppliers.

We select the month and year, the measure will calculate YTD purchases for suppliers and then use PERCENTILEX.INC to calculate the quantiles.

The PERCENTILEX.INC function will calculate the YTD Purchase boundaries for the quantile boundaries.

For example, for month 6, if I have 100 suppliers, they are ranked from lowest to highest spend. The function will then go to the supplier 25th position and identify how much they spent, say $150. This means that the lowest 25% (Q1) spent $150 or less.

If I select month 12, I may have 500 suppliers. In which case, Q1 will be 1/4 of population which is 100. Therefore function will go to the 100th position after sorting list low to high and identify spend value, say $75. This means that the lowest 25% (Q1) have spent $75 or less.


Problem
I want to build a matrix visual with the quantile ranges on the rows and the values as total YTD spend. I have already built a measure for the YTD spend and can confirm it works. For the rows, I used a disconnected table with the quantile ranges, so it it would have Q1,Q2,etc. These values are static but I want to replace these with dynamic ranges using DAX.

So instead of the axis name being called RQ1, it would be 1-39 and so forth.

At the moment, I can only build a dynamic range and put in table as values - I cant put into rows or columns because its a measure.

Question - is this even possible? I havent seen any examples of this. Ideally I want to turn this into a chart and was thinking of using Deneb, but I wanted to see what I could do with a native visual, for example a bubble chart where each quantile is placed evenly on the x-axis and the size of the bubble is determined by the value of YTD spend. Each quantile will have a dynamic label to let users know the distribution.

Dont need someone to write the code for me, but if someone has a blog or another post that I could look or has come across a similar issue, I would appreaciate if they could post their thoughts.

Thank you in advance!

[lbendlin · Accepted solution]

Power BI does not support dynamic bucketing. You need to bring your own static buckets, in a disconnected table, so you can use that for your x axis.  Then use measures to fill the buckets as needed.

[Anonymous]

I thought as much..it seems like that. That DAX is going to be complicated 😞