This is a case that I have come across with one of my customers very recentely. I wanted to calculate sum of sales generated by first N products pareto style i.e. if first product’s sales is 200 and second’s 100, my calculation would show 200 for the first product and 300 for second. This allowed me to see that e.g. 90% of our total sales are generated by 7 products.

Initially, I had an approach using RANKX. But due to the volume of data (more than 300 SKUs dynamically selected and milions of rows) the performance was very poor. RANKX had to be calculated over each cell multiple times to select those products that I want. I had to find another approach and luckly this post provided me with some inspiration. The measure I have come up with:

Cumulative Total =
VAR total_sales = [Total Sales]
RETURN
IF (
NOT ( ISBLANK ( total_sales ) ),
CALCULATE (
[Total Sales],
FILTER ( ALLSELECTED ( Sales[Product] ), total_sales <= [Total Sales] )
)
)

Let’s see it in action. I have a simple table with different products and their sales: I create a simple measure [Total Sales] = SUM(Sales[Sales]) and I also create my Cumulative Total measure. If I now create a table visual with my two measures I get: Exactly, as I want I get cumulative total starting with the best product. If I select only products B,F,C, I get: So, how does it work?

VAR total_sales = [Total Sales]

This will calculate and “save” sales for each product into a variable. Remember that variable is calculated once at the beginning. So in the case with products B,F,C, total_sales with be 400, 315, 243.

IF ( NOT ( ISBLANK ( total_sales ))

This is to make sure that we don’t include products with zero sales.

CALCULATE ([Total Sales],

This will calculate total sales for all the products that we filter in the next step.

FILTER ( ALLSELECTED ( Sales[Product] ), total_sales <= [Total Sales] )

This is the most imporant, but also weirdest part of the measure. First of all, it is important to note that FILTER is a function that returns a table that represents a subset of another table or expression. In our case the table is ALLSELECTED ( Sales[Product] ). Next, the function goes over every item in said table (in the second example that would be B,F,C) and checks if total_sales (which is sales of the item in the current filter context in table visual) is smaller or equal to its Total Sales. Then it picks all products that meet the requirement and calculate the sales.

This might be tricky to grasp at first so let’s focus on the second example: The way DAX works, is the measure is calculated for each cell seperately, as they are in different filter context. So we will have separate calculations for products C,F,B.

Let’s calculate this for C:

total_sales (as explained above) is going to be 243

NOT(ISBLANK(total_sales) is true, as 243 isn’t blank

CALCULATE([Total Sales] depends on the filter function

FILTER(ALLSELECTED(Sales[Product]) has a small table with one column [Product] and rows C,F,B. It wants to check if [Total Sales] is higher or equal to 243. So for C, I have [Total Sales] = 243 and this is indeed equal to 243. For F, [Total Sales] is 315 and it is higher than 243. For B, [Total Sales] is 400, which is higher than 243. This filter will see that all C,F,B meet the requirement and calculate [Total Sales] for all of them, which is 243+315+400=958.  

Let’s calculate this for F:

total_sales (as explained above) is going to be 315

NOT(ISBLANK(total_sales) is true, as 315 isn’t blank

CALCULATE([Total Sales] depends on the filter function

FILTER(ALLSELECTED(Sales[Product]) has a small table with one column [Product] and rows C,F,B. It wants to check if [Total Sales] is higher or equal to 315. So for C, [Total Sales] = 243 and this is smaller than 315. For F, [Total Sales] is 315 and it is equal to 315. For B, [Total Sales] is 400, which is higher than 315. This filter will see that F,B meet the requirement and calculate [Total Sales] for them, which is 315+400=715.  

Let’s calculate this for B:

total_sales (as explained above) is going to be 400

NOT(ISBLANK(total_sales) is true, as 400 isn’t blank

CALCULATE([Total Sales] depends on the filter function

FILTER(ALLSELECTED(Sales[Product]) has a small table with one column [Product] and rows C,F,B. It wants to check if [Total Sales] is higher or equal to 400. So for C, [Total Sales] = 243 and this is smaller than 400. For F, [Total Sales] is 315 and it issmaller than 400. For B, [Total Sales] is 400, which is equal to 400. This filter will see that only B meets the requirement and calculate [Total Sales] for B, which is 400.

I believe this is the most efficient way to calculate this type of measure, espacially that it does not depend on RANKX.

Thanks