Dynamic Percentile Matrix

Question

Greetings!&nbsp;I am trying to build a percentile matrix that allows the user to slice it by year, sales channel, business unit, category, sub-category, etc.&nbsp;Below is what I am trying to build in Power BI:Slicer 1Slicer 2Slicer 3Slicer 4Line-ItemMin25th Percentile50th Percentile75th PercentileMaxVolume##########Gross Sales$$$$$$$$$$Net Sales$$$$$$$$$$Inventory COGS$$$$$$$$$$Other COGS$$$$$$$$$$Freight$$$$$$$$$$Warehouse$$$$$$$$$$Gross Margin$$$$$$$$$$EBIT$$$$$$$$$$Contribution Margin$$$$$$$$$$&nbsp;Here is a general idea of what my fact table looks like:&nbsp;DateCategory_IDSub-Category_IDVolumeGross SalesNet SalesInventory COGSOther COGSFreightWarehouseGross MarginEBITContribution Margin1/1/2021AA1##$$$$$$$$$$$$$$$$$$1/1/2021AA1##$$$$$$$$$$$$$$$$$$1/1/2021AA1##$$$$$$$$$$$$$$$$$$1/1/2021AA2##$$$$$$$$$$$$$$$$$$1/1/2021AA2##$$$$$$$$$$$$$$$$$$1/1/2021BB1##$$$$$$$$$$$$$$$$$$1/1/2021BB1##$$$$$$$$$$$$$$$$$$1/1/2021BB2##$$$$$$$$$$$$$$$$$$1/1/2021BB2##$$$$$$$$$$$$$$$$$$1/1/2021BB2##$$$$$$$$$$$$$$$$$$1/1/2021CC1##$$$$$$$$$$$$$$$$$$1/1/2021CC1##$$$$$$$$$$$$$$$$$$1/1/2021CC2##$$$$$$$$$$$$$$$$$$1/1/2021CC2##$$$$$$$$$$$$$$$$$$&nbsp;I've looked around and tried a few of my own techniques but I can't seem to crack this nut.&nbsp;Does anyone have any good ideas on how I can achieve this?&nbsp;Thank you!

allurean · Answer

Hi,&nbsp;Anonymous&nbsp;Please check this example:&nbsp;https://www.dropbox.com/s/ks3dsezceq5nyh6/Dynamic%20Percentile%20Matrix%28allure-analytics.com%29.pbix?dl=0&nbsp;Did I answer your question? Please Like and Mark my post as a solution if it solves your issue. Thanks.Appreciate your Kudos !!!https://allure-analytics.com/https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpAhttps://www.linkedin.com/company/77757292/

anonymous · Answer

Hi&nbsp;ALLUREAN&nbsp;&nbsp;Thank you for providing me with this solution!&nbsp;Are we able to achieve the same outcome without unpivoting the original fact table?&nbsp;The reason being, is that I have a lot of other measures on top of that fact table and unpivoting would require me to re-write all the DAX code I have.

allurean · Answer

Go to matrix options, under Values should have Show on rows. Not sure this will solve it, depends on your data

anonymous · Answer

But the measures you provided are dependent on the unpivoted table and not the original table. Am I missing something?

Forum Discussion

Dynamic Percentile Matrix

4 Replies

Related content

Dynamic calculation of percentiles

Matrix with dynamic values

Dynamic Column Header In a Matrix

Dynamic Percentile and Sum Above 80th Percentile

Dynamic classification basing on percentiles

Line-Item	Min	25th Percentile	50th Percentile	75th Percentile	Max
Volume	##	##	##	##	##
Gross Sales	$$	$$	$$	$$	$$
Net Sales	$$	$$	$$	$$	$$
Inventory COGS	$$	$$	$$	$$	$$
Other COGS	$$	$$	$$	$$	$$
Freight	$$	$$	$$	$$	$$
Warehouse	$$	$$	$$	$$	$$
Gross Margin	$$	$$	$$	$$	$$
EBIT	$$	$$	$$	$$	$$
Contribution Margin	$$	$$	$$	$$	$$