Dynamic Pareto table based on cumulative %

[k_swathi2001]

 Hi,

I am working on Pareto table which shows the Product Grade and units based on 80/20 rule. I would like to have a parameter where I can change the 80/20 % and check to see Product grade based on 70/30 or 60/40. Is it possible to do it? I tried using what-if parameters but I was not able to acheive the dynamic % change. I am using below formula in a new colulmn and I want to use a parameter instead of static numbers.

I am unable to attach the pbix file for sample purpose. 

Any help on this is appreciated.

Thanks
KS

[v-yadongf-msft]

Hi @k_swathi2001 ,

You can create various percentage combinations for end users. I don't think "the user wants to enter his own % to check the impact" is possible.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yadongf-msft · Accepted solution]

Hi @k_swathi2001 ,

Please check "Field parameters" in Preview features.  Then restart Power BI for the function to take effect.

Please try following DAX to create three measures:

Grade(80/20 %) = SWITCH(
    TRUE(),
    MAX('Table'[Cumm Pct])<=0.6,"A+",
    MAX('Table'[Cumm Pct])>0.6 && MAX('Table'[Cumm Pct])<=0.8,"A",
    MAX('Table'[Cumm Pct])>0.8 && MAX('Table'[Cumm Pct])<0.9,"B","C")

Grade(70/30 %) = SWITCH(
    TRUE(),
    MAX('Table'[Cumm Pct])<=0.3,"A+",
    MAX('Table'[Cumm Pct])>0.3 && MAX('Table'[Cumm Pct])<=0.7,"A",
    MAX('Table'[Cumm Pct])>0.7 && MAX('Table'[Cumm Pct])<0.9,"B","C")

Grade(60/40 %) = SWITCH(
    TRUE(),
    MAX('Table'[Cumm Pct])<=0.4,"A+",
    MAX('Table'[Cumm Pct])>0.4 && MAX('Table'[Cumm Pct])<=0.6,"A",
    MAX('Table'[Cumm Pct])>0.6 && MAX('Table'[Cumm Pct])<0.9,"B","C")

Click "Modeling"-"New parameter"-"Fields".

Check three measures and click "Create".

You will get a field parameter like below:

Create a slicer using field parameter and create a table visual using ID column and field parameter.

You can change the 80/20 % and check to see Product grade based on 70/30 or 60/40. 

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[k_swathi2001]

This is a good solution I can use it for now, but what if the end user wants to enter his own % to check the impact? I mean I have a request where the user wants to  enter any value for % for A+,A,B,C individually and check. 

for e.g. The request I got is , the user wants to check  how many products fall under each Grade by changing the %. 

This pic is based on 60/20/10 % 

This is based on 70/15/10 %

Basically the user wants to enter his own % to check the impact.

Thanks