Pareto Rule - DAX Function


#1

Hey Sam
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 ?


#2

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 ))


#3

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


#4

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


#5

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 ) ) )