Pareto Analysis & Measures

[corange]

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. 

[v-xiaotang]

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

[YukiK]

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]

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