Forum Discussion

k_swathi2001's avatar
k_swathi2001
Regular Visitor
4 years ago
Solved

Dynamic Pareto table based on cumulative %

 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

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    4 years ago

    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.

     

3 Replies

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      k_swathi2001
      Regular Visitor
      4 years ago

      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

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.