Dynamic Percentile Matrix

[Anonymous]

Greetings!

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.

Below is what I am trying to build in Power BI:

• Slicer 1
• Slicer 2
• Slicer 3
• Slicer 4

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	$$	$$	$$	$$	$$

Here is a general idea of what my fact table looks like:

Date

Category_ID

Sub-Category_ID

Volume

Gross Sales

Net Sales

Inventory COGS

Other COGS

Freight

Warehouse

Gross Margin

EBIT

Contribution Margin

1/1/2021

A

A1

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

A

A1

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

A

A1

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

A

A2

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

A

A2

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

B

B1

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

B

B1

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

B

B2

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

B

B2

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

B

B2

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

C

C1

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

C

C1

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

C

C2

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

1/1/2021

C

C2

##

$$

$$

$$

$$

$$

$$

$$

$$

$$

I've looked around and tried a few of my own techniques but I can't seem to crack this nut.

Does anyone have any good ideas on how I can achieve this?

Thank you!

[ALLUREAN]

Hi, @Anonymous 

Please check this example:

https://www.dropbox.com/s/ks3dsezceq5nyh6/Dynamic%20Percentile%20Matrix%28allure-analytics.com%29.pbix?dl=0

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_JWYLEmP6KrpA

https://www.linkedin.com/company/77757292/

[Anonymous]

Hi @ALLUREAN 

Thank you for providing me with this solution!

Are we able to achieve the same outcome without unpivoting the original fact table?

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]

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

[Anonymous]

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