Pareto Rule, ABC Class in DAX

The pareto rule can be applied to almost all areas of life. Here, we are going to explore how we can calculate it in DAX, efficiently. Starting with the source calculation, which is adapted from the Dynamic – ABC Classification from the guys of SQLBI.com.

With the addition of variables in DAX some calculations can be made much more efficient. Therefore, the calculation I will propose has been made a little bit more efficient because it was rewritten for DAX 2.0.

Therefore, making two variable tables of the same data (different naming), and calculating them once instead of every iteration will optimize the calculation in a significant way.

Cumulated percentage =
VAR Outer =
    ADDCOLUMNS ( VALUES ( ‘Customer'[Customer] ); “OuterValue”; [Amount] )
VAR Inner =
    ADDCOLUMNS ( ALL ( ‘Customer'[Customer] ); “InnerValue”; [Amount] )
RETURN
    SUMX (
        CALCULATETABLE (
            ADDCOLUMNS (
                Outer;
                “CumulatedPercentage”; DIVIDE (
                    SUMX ( FILTER ( Inner; [InnerValue] >= [OuterValue] ); [InnerValue] );
                    CALCULATE ( [Amount]; VALUES ( Customer[Customer] ) )
                )
            );
            ALL ( Customer )
        );
[CumulatedPercentage]
    )

This calculation is much more efficient than the initial one, and it let’s you apply cumulative percentage to any kind of dimension (Customer, Product, Account) dynamically. For example to show 80% of sales/revenue/margin over a dimension.

Additionally you can extend the Pareto calculation to the ABC classification calculation. The dimension classified is still Customer, meaning we are classifying which customers (sales) amount comprises of 70% of whole sales, which 20% and the rest 10%. Classifying them as one of the three classes A, B or C.

Amount ABC =
VAR Outer =
    ADDCOLUMNS ( VALUES ( ‘Customer'[Customer] ); “OuterValue”; [Amount] )
VAR Inner =
    ADDCOLUMNS ( ALL ( ‘Customer'[Customer] ); “InnerValue”; [Amount] )
RETURN
    CALCULATE (
[Amount];
        VALUES ( ‘Customer'[Customer] );
        FILTER (
            CALCULATETABLE (
                ADDCOLUMNS (
                    Outer;
                    “CumulatedPercentage”; DIVIDE (
                        SUMX ( FILTER ( Inner; [InnerValue] >= [OuterValue] ); [InnerValue] );
                        CALCULATE ( [Amount]; VALUES ( Customer[Customer] ) )
                    )
                );
                ALL ( Customer )
            );
[CumulatedPercentage] > CALCULATE ( MIN ( ‘ABC Class'[Value min] ) )
&& [CumulatedPercentage] <= CALCULATE ( MAX ( ‘ABC Class'[Value max] ) )
        )
    )

With such a calculation we can compare a customer (or any other dimension) performing over different regions, or over time. Dynamically, just by a selection of the fields and visual type, because the calculation is set!

Also, the pattern can be used on a large scale data model, because with the help of variables it is made efficient.

You can download and play with the pattern here – Pareto Rule.

You can download and play with the pattern here – ABC Classification.