Livio/ October 14, 2018/ DAX, Power BI/ 1 comments

Dynamic Percentiles using DAX

Using DAX, it is not so difficult to find out who your Top 25% Customers are for a given product and what is the Sales Amount of these Top customers. 

Download the file

The model I will use is a simple one, the classic Adventure Works:

The first measure I will write will determine the Top 25% Sales of the Customers within each Category and Subcategory:

As you can see, this first measure is not additive because the percentile amount is being calculated for each combination of Product Category and Product Subcategory. The measure is composed of the following steps:

1. Determine the list of customers with sales in the current context. This is done by doing: VAR CustomersTable = CALCULATETABLE( VALUES( Customers[CustomerKey] ), Sales )
2. Calculate the 0.75 percentile using the PERCENTILEX iterator function: =
   VAR PercAmount = PERCENTILEX.INC (
              CustomersTable,
              [TotalSales],
             0.75
     ) – where [TotalSales] is a measure I have previously defined as  =SUM(Sales[SalesAmount] ).
3. Get the list of customers whose sales in current context are equal to or above the Percentile: VAR TopCustomers =
                             FILTER(
                                 CustomersTable,
                                [TotalSales] >= PercAmount
                             )
4. Calculate the Sales amount in the current context only for the Top Customers: =CALCULATE( [TotalSales], TopCustomers )

The complete measure will be:

If instead, by using the same matrix, you wanted to see the top 25% Customers Sales per Category and see how these were distributed within each Subcategory you would change slightly the measure to:

 This would yield you the following results (which now are additive per each category unlike before):

The difference between this last measure and previous one is that we are computing the Top 25 % Customers list at the Product Category level by removing the filter coming from the SubCategory table ( but keeping the one coming from the Category table ) by adding: ALLEXCEPT( ProductSubCategory, ProductCategory ). 

Thanks to this, now you would be able to slice your data by Year to see the sales of the Top 25% customers by year and by category and see how they are distributed within each Subcategory:

This approach allows for deeper analysis, because now, by modifying slightly our measure, we can find out who are the Top % customers and how they distributed by Gender, or Occupation or Marital Status etc.Furthermore, the percentile can be made dynamic by adding a WhatIf Parameter which allows the user to change dynamically the Top percentage by using the below set up:

New measure:

It is also worth nothing that, by using a disconnected set up table, you could define Percentiles or Quartiles and see how Customers are distributed within each Quartile: Sales amount of Top 25% compared with Sales amount of Bottom 25% and so on…