Create Cumulative Totals, No Date, No Index - Advanced DAX Techniques in Power BI

This is another great example of how advanced you can get with DAX formulas in Power BI. I showcase here how to create Cumulative Totals in Power BI, but not using any dates.

This is advanced DAX.....

I spend some time detailing the steps that you need to work through to complete this in Power BI. There are a few but when done together they enable a very unique and powerful insights.

There are many custom applications for this type of analysis. Good luck implementing this in your own models.

***** Learning Power BI? *****

[daveportland]

Great video. An alternative solution which does not require ranking at all:
Cumulative total=
SUMX(
FILTER(
SUMMARIZE(
ALLSELECTED(product),
product [ product name ],
"SALESinsideSummarize", [total Sales],
)
( [total Sales], VALUES (product, [ product name] ) )
)
[total sales]
)

[alexabell]

This is SO helpful! It should solve a request I've received that essentially asks for an 80 / 20, Pareto analysis.

[Victor-ollo]

Absloutely awesome logic !!! Thanks Sam !! This kind of stuff lifts average DAX users to a different level.... Thumbs UP !!!

[cesarsaldana]

The technique does not work correctly if they have several equal sales, because it assigns the same rank to several products.
therefore it is necessary to create an alternate index to tie the tie.

Grettings From Mexico.

[parnpanworranut]

This is so helpful after 5 years of uploading. Appreciate it so much. Could you help us a little bit more on a cumulative percentage, please?

[mnowako]

GREAT! This really blew my mind! Many thanks Sam!

[massefromla]

Running total, the last formula .. the cumulative total . I used Calculate and it is much simpler than Sumx, using iterative .. it is better for performance to use CALCULATE

[vishiousvish]

thx i want to do this and is the only post i find about it !!!!

[tanababa]

Thank you,
Got my head all spanned :-)
Will have to work it out slowwwwly...

[GoodlyChandeep]

Sam, the product name measure might return blank if the Filter function returns 2 names with same sales amount.

[damienlomath]

Good video, how about you have so many slicers, like Regions, Districts, and Dates, How will u go about it

[gilgarnsey]

This is truly amazing, thank you. I do have two questions...

[billsewell]

This approach finally helped me solve my running total based on ranked sales per organization. I was finally able to segment my orgnazations into Top 50% of Revenue, mid 30% of Revenue and Bottom 20% of Revenue. The challenge is that I also want to use these measures in cards and with other measures... ie. what's the average deal value of companies in the lowest 20%. It seems like this strategy is only good for referring to lists. Where would I start crunching this down to card level presentations?

[eladiobardelli]

Acumulado =
var serie = max (tabla1 [N] )
var resumen = filter ( allselected ( tabla1), tabla1 [N] < = serie )
return
sumx ( resumen, [campo montos] )

[whatsgoingonization]

Hi Sam, I worked through this formula and setup, but at the end, my cumulative column is returning the cumulative value, but only in the first row-ie it is not iterating throughout the table. Any idea why?

[rogervandecraen]

I tried to reproduce the measures on a similar dataset ( generated random product# and random sales )
I spend some time but the measure always gave awrong result. The problem was the dubplicate product#.
In the table there were duplicates poduct# which causes the suggested measure did not work proprerly.
First you have to generate a virtual table with unique product#/sales within the measure to get the correct result .
I can sent the pBbix file if you're interested.

[anantmehta]

This is brilliant and so well explained. Thank you!

[dham]

Really nice tutorial!


To make this kind of ranking is necessary to have a table with the totals of each product?

For the case in which you do not have those totals, what formula can I use?


Thanks!

[adamsobanski]

Hello, I have to say thank you in the first place. The solution is great and like you've said, DAX is really powerful.
I have a question about performance - I've got a lot of products (approx 790k) and I am running direct query. There is an error with not enough memory, even though I have a solid PC with enough RAM.
Is there a way to optimize performance in this case?
Thank you!

[alpakdeniz]

Hey Sam very interesting video! Just wanted to make sure I understood this though: So if I understand this correctly SUMMARIZE() works like ALL() where in this case it ignores the current evaluation context (the product name) on the table to give you a virtual table with a full list of product names? Just want to make sure I understood this correctly because that is extremely powerful! Thanks! - Alp