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.

Pareto-80.png
Which customers comprise of 80% of all sales

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

ABC classified customers
Customers by Class overall (Class A being 70% of sales, B – 20%, and C – 10%)

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!

ABC class over time
ABC class over time

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.

One thought on “Pareto Rule, ABC Class in DAX

  1. Hi Jani,

    I have read this article a while ago when it was published and it looked like a great approach. Today I have tested it in the real world scenario with the dataset of over 14 mil rows and execution time is around 25 min.
    I think it would be worth reconsidering approach for big datasets. If I come with the working solution I’ll let you know.

    regards

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s