Calculating cumulative total across categories

[tomerb]

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?

[Anonymous]

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

[tomerb]

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

[Anonymous]

Hi @tomerb ,

What are your expected results?

Best regards,
Community Support Team_ Scott Chang

[tomerb]

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. 🙂

[lbendlin]

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

[tomerb]

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!

[tomerb]

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.

[lbendlin · Accepted solution]

Why would you expect 21?

[tomerb]

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

[Jihwan_Kim]

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] )
    )
)

[tomerb]

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?

[lbendlin]

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

[tomerb]

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?

[lbendlin]

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-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[tomerb]

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