Forum Discussion

corange's avatar
corange
Icon for Post Patron rankPost Patron
4 years ago

Pareto Analysis & Measures

Hi everyone, 

 

I am after some recommmendations / directions. 

 

I have created a Pareto Analysis on POWER BI using the following measures: 

 

  • Total_Amount = SUMX(JobDetails, JobDetails[ServiceCat])
  • Rank_All = RANKX(ALL(JobDetails[ServiceCat]), [Total_Amount])
  • Pareto_Value = SUMX(TOPN([Rank_All], ALL(JobDetails[ServiceCat]), [Total_Amount]), [Total_Amount])
  • Amount_All = CALCULATE([Total_Amount], ALL(JobDetails[ServiceCat])
  • Pareto_% = DIVIDE([Pareto_Value], [Amount_All], BLANK())

 

It works well however, when I add other measures in the table (e.g. daily averages) or a field from the table, the ranking goes wrong and doesnt work anymore. Do you know what needs to be done to avoid this? 

 

Example of measures I am trying to add to the table are: 

 

  • Daily Avg Job= DIVIDE(SUM(Jobs_Details'[Is Countable]),SUM(vw_Calendar_BI[IsTradingDay]),0)
  • Avg Job Value =DIVIDE(SUM(Jobs_Details'[TOTAL_CHARGE]), SUM(Jobs_Details'[Countable]),0)
 
 
Thank you. 
Need Help

3 Replies

  • YukiK's avatar
    YukiK
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    If the purpose is to get the rank over the whole table, then you may use RANKX(ALL(JobDetails), [Total_Amount]) instead of what you have?

    • corange's avatar
      corange
      Icon for Post Patron rankPost Patron
      4 years ago

      I would like to be able to use filter and categorise it by different field, like clients, service type and rates card. 

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

    Hi corange 

    Not sure what's your data and the relationship between table, so I create a sample to test, however I didn't reproduce the wrong behavior,

     

    Could you share a sample file for us to test? 

     

    Kindly Note: you can replace raw data with bogus data to protect your sensitive information. Thanks 🙂

     

     

    Best Regards,

    Community Support Team _Tang

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