Pareto Rule - DAX Function

Hey Fellow Power BI Users -

Just looking at your example online.
Just curious as to why I have a static total. I feel this is the exact same as yours too, but your number updates to Top 20…
I DAX also seem the same as yours too, or is it me ?

You seem to be using a different formula.

This one below works fine in the example

Sales of Top 20% of Customers =

VAR
CustomerPercent = DISTINCTCOUNT( Sales[Customer ID] ) * 0.2

RETURN
CALCULATE( [Total Sales],
     FILTER( VALUES( Sales[Customer ID] ),
          RANKX( VALUES( Sales[Customer ID] ), [Total Sales], , DESC ) <= CustomerPercent ))

Hi Sam

The below video you show a different technique as this below way I can see the 20% of my customers.
I can drag this measure and see it based on my customers.

However i’ve used exactly same format of DAX as you, and my total itself is wrong

So Sam
Just confirming doing a Pareto this way, I am just confused because I am not sure this actually shows who exactly are the 20% of the customers are. If i dragged below measure on my customer field it goes blank.
Hence why I am trying to use a DAX like in my 2nd screen shot.

Thanks in advance.

Shrik

Ok sorry, understood now.

Try this formula below, I’ve optimized it for this

Top 20% Customer = 
VAR CustomerRank = RANKX( ALL( Customers ), [Total Sales], , DESC )
VAR UniqueCustomers =  CALCULATE( [Unique Customers], ALL( Customers ) )
VAR Top20 = UniqueCustomers * 0.2

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

1 Like