# 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 (
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 (
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] ) )
)
) 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!

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.

## One thought on “Pareto Rule, ABC Class in DAX”

1. Augustin says:

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