Pareto / TOPN/ Tree Map issue

Hello @Matty,

My sincere apologies.

Actually I thought in your file “Customer Ranking” was calculated based on Sales Table so in my formula also I by mistake referenced it as “Sales” instead of “Customers” and due to referencing error on my part the individual line items total showed correctly but the “Grand Total” received an error.

Below are the changed formula for the reference -

Part 1 : Calculation of 20% Using Topn

20% Using TOPN 1 - Harsh = 
VAR CustomerRank = RANKX( ALL( Customer ), [Total Sales], , DESC )
VAR UniqueCustomers = CALCULATE( DISTINCTCOUNT( Customer[Customer Name] ) , ALL( Customer ) )
VAR Top20 = UniqueCustomers * 0.2

RETURN
IF( ISFILTERED( Customer[Customer Name] ) , 
    IF( CustomerRank < Top20 , [Total Sales] , BLANK() ) , 
        CALCULATE( [Total Sales] , TOPN( Top20 , ALL( Customer ) , [Total Sales] , DESC ) ) )

Alternatively -

20% Using TOPN 2 - Harsh = 
VAR Top20 = CALCULATE( DISTINCTCOUNT( Customer[Customer Name] ) , ALL( Customer ) ) * 0.2

Return
IF( ISFILTERED( Customer[Customer Name] ) , 
    IF( [Customer Ranking] < Top20 , [Total Sales] , BLANK() ) , 
        CALCULATE( [Total Sales] , TOPN( Top20 , ALL( Customer ) , [Total Sales] , DESC ) ) )

Part 2 : Calculation of 20% Using Topn - Total

20% Using Topn Total - Harsh = 
SUMX( 
    SUMMARIZE( Customer , Customer[Customer Name] , "Topn Totals" , [20% Using TOPN 1 - Harsh] ) , 
        [Topn Totals] ) 

Please Note: I’ve used Measure Branching Technique to Fix the Totaling Errors.

And as per my calculation the Total which shows for “20% Using Topn” is equal to “Top 29 Customers” instead of “Top 27”.

I’m providing the PBIX working of my file for the reference as well as a few links of videos created by Sam showing how to fix totaling errors in Power BI.

Hoping you find this useful and helps you to understand how you can fix the totaling errors. :slightly_smiling_face:

Thanks & Warm Regards,
Harsh

Pareto.pbix (109.1 KB)

https://portal.enterprisedna.co/courses/305959/lectures/11981612

https://portal.enterprisedna.co/courses/305959/lectures/13664651