topic Re: Calculating cumulative total across categories in DAX Commands and Tips

Calculating cumulative total across categories

tomerb — Fri, 13 Dec 2024 23:17:47 GMT

I'm using this to calculate cumulative/running total across categories, where the sum of each category is added to the next category in line, based on a pre-defined order:

Running Total = VAR CurrentCategoryOrder = MAX(TABLE[CATEGORY_ORDER]) RETURN CALCULATE( SUM(TABLE[VALUE]), FILTER( ALL(TABLE[CATEGORY], TABLE[CATEGORY_ORDER]), TABLE[CATEGORY_ORDER] <= CurrentCategoryOrder ) )

This works most of the time, except for when no data for a category exists for a specific month, in which case, instead of the cell showing just the sum of all previous categories, it is showing 0.

Any suggestions on how to handle this correctly?

Re: Calculating cumulative total across categories

lbendlin — Sat, 14 Dec 2024 01:42:22 GMT

To report on things that are not there you need to cross join disconnected tables and use measures.

Re: Calculating cumulative total across categories

Jihwan_Kim — Sat, 14 Dec 2024 10:17:35 GMT

Hi,

I am not sure how your semantic model looks like, but please try something like below whether it works.
I experimented by using the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

Running Total = CALCULATE ( SUM ( 'Table'[value] ), WINDOW ( 1, ABS, 0, REL, ALL ( 'Table' ), ORDERBY ( 'Table'[date], ASC, 'Table'[category_order], ASC ), , , MATCHBY ( 'Table'[category_order], 'Table'[date] ) ) )

Re: Calculating cumulative total across categories

tomerb — Sat, 14 Dec 2024 14:14:59 GMT

Thank you for the response.

Do you mean I should create a helper table that has all the categories and their mapped order, then join that with the data table I have? If so, can you point me to a documentation, or some other data point that help me get started?
On that matter, perhaps it's easire to go back to the data ingestion pipeline, and add dummy items for months that have "missing" data?

Re: Calculating cumulative total across categories

tomerb — Sat, 14 Dec 2024 14:20:49 GMT

Thank you for the response.

I looked at the file you shared. In your example table a value exists for the entire set of categories for each month. This means that it might be that for a certain month category X has the value 0, in this case the running total indeed will get calculated.

However, in my case, I have missing categories for some months. This means that category X doesn't exist for that month, and in this case the running total will not get added to that month and category - I validated this in your dataset by removing the last row from the table.

Perhaps something along the line of what lbendlin is suggesting is the only way to go?

Re: Calculating cumulative total across categories

lbendlin — Sun, 15 Dec 2024 01:34:18 GMT

adding dummy data is not a preferred approach in most cases.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Calculating cumulative total across categories

tomerb — Sun, 15 Dec 2024 09:46:13 GMT

Please see the this file, based on Jihwan_Kim reply, with a modified measure to match the one I'm using.

Basically, when all the categories for all months exist in the data set, there's no issue, as shown here:

But if in one of the months an entry with one category is missing comletely, the data for that cell will be missing too, and this is what I get:

In this case, I removed the last row from the generated table, which means that category 'd' doesn't exist in the dataset for December. What I expect to see instead of a blank cell, is the value 16, since this is the running total for that date.

Re: Calculating cumulative total across categories

tomerb — Sun, 15 Dec 2024 09:55:51 GMT

It looks like I accidentally replied to my own thread, instead of your comment. Please find my response here.

Re: Calculating cumulative total across categories

lbendlin — Mon, 16 Dec 2024 00:25:01 GMT

Why would you expect 21?

Re: Calculating cumulative total across categories

tomerb — Mon, 16 Dec 2024 07:29:21 GMT

I would not expect 21, I excpet 16 instead of an empty cell.

Re: Calculating cumulative total across categories

Anonymous — Tue, 17 Dec 2024 02:58:02 GMT

Hi tomerb ,

lbendlin seems to have solved the problem, and if nothing else, you can mark his solution as a solution for other members to find.

Best regards,
Community Support Team_ Scott Chang

Re: Calculating cumulative total across categories

tomerb — Tue, 17 Dec 2024 07:37:24 GMT

Unless I'm missing something, this issue is not solved yet. Please point me to the comment you think provide a solution.

Re: Calculating cumulative total across categories

Anonymous — Tue, 17 Dec 2024 07:41:33 GMT

Hi tomerb ,

What are your expected results?

Best regards,
Community Support Team_ Scott Chang

Re: Calculating cumulative total across categories

tomerb — Tue, 17 Dec 2024 09:24:38 GMT

My apologies. I missed the fact that lbendlin attached a file and a screenshot, and just referred to his comment. I see the solution provided now.

This is a great answer, thanks lbendlin. The issue I have with it is that it forces me to use the static table in the Rows section of the table, which doesn't work with real data. Any suggestion on how to cope with that?
To provide more info about the model, the main table ('Table' in our example) holds multiple columns which form a hierarchy I use in the Rows section of the matrix. If I mix the categories from Table2 with this data, the data gets duplicated in the table.

Does it mean that I'll need to duplicate the data from the columns I need? This means that effectively I'll need to duplicate the entire data table, and all just for the table to act reasonably. 🙂

Re: Calculating cumulative total across categories

lbendlin — Tue, 17 Dec 2024 12:26:14 GMT

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Calculating cumulative total across categories

tomerb — Wed, 18 Dec 2024 10:55:01 GMT

I will mark this thread as solved, and will create a new one with the requested data, as it might be a bit of a variation from the initial question.

Thank you for your help!