Calculating cumulative total across categories with multiple levels

[tomerb]

This is a follow-up question to this thread.

I am trying to calculate a running total between categories for several months, with some months missing a category (or several categories), in which I want to show the value from the accumulated previous months.

This is currently the DAX used to calculate the running total:

Running Total = 
VAR CurrentCategoryOrder = MAX('StaticTable'[CategoryOrder])
RETURN
CALCULATE(
    SUM('DataTable'[Val]),
    FILTER(
        ALL('DataTable'[Category], 'DataTable'[CategoryOrder]),
        'DataTable'[CategoryOrder] <= CurrentCategoryOrder
    )
)

Note that StaticTable is a static list of all possible categories and their respective order, and DataTable holds the data using these categories + values/date entries. See the complete pbix file here.

I'm looking for guidance in understanding if this is actually the way to go here, as this is a variation from what @lbendlin suggested in the previous thread.

Also, there still seems to be an issue with the way the data is presented: the table actually doesn't show the real value of the leaf nodes, as the running total is applied to the leafs too. Is there's a way to distinguish each level from the one above, still showing the correct value for each hierarchy?

Data for JanuaryView for January

[lbendlin]

To report on things that are not there you need to use disconnected tables and/or crossjoins

[tomerb]

Thanks for the reply. However, again the table is not showing the values of the leaf cells.

The idea is that each level in the hierarchy shows the sum of the lower levels, but also each category (upmost level) is showing a running total, based on CATEGORY_ORDER.

So in this example, for January there are three entries, and what I expect to see is the values 10 and 5 for the leafs under category 'a', then the value 15 in L2_A, then the value 15 for L1_A, then the value 15 for 'a'.

Level 'b' and 'c' should also have the value 15, but then since level 'd' has value in one of its children, it should have the value 25 (previous 15 + new 10).

[lbendlin]

I'm afraid I don't understand what the expected outcome is (and why).  Can you please elaborate again?

[Mark-JZ-Yeap]

DataTable

Dates

StaticTable

Formula of Running Total as DAX measure:
Running Total =

VAR md = MAX(Dates[Date])

VAR CurrentCategoryOrder = MAX('StaticTable'[OrderID])

RETURN

SUMX(

    FILTER(

        ALL('DataTable'),

        'DataTable'[Date] <= md &&

        'DataTable'[OrderID] <= CurrentCategoryOrder

    ),

    'DataTable'[Val]

)

This is the result.

Is it correct?

[tomerb]

I actually found something similar to what I'm trying to do here.

Notice how the categories are accumulated between one layer and the other, and each sub-category (leaf) is showing the amount specific for that leaf.

[tomerb]

Thank you, Dengliang Li.

The rows actually don't add up, as the sum of the children should be equal to the parent's value, which is not the case here. I'm actually not sure what numbers we're seeing here.

Also, can you please explain why we need the static, artificial LevelX Value columns? The same values are duplicated for each row.

[Anonymous]

Hi @tomerb ,

Since I am not sure how the values of the child nodes are stored in the source data table.
Therefore, I added the LevelX Value column as an example to show the values of the child nodes.

In my previous reply, I first provided a way to differentiate between levels and then display values for each hierarchy via the LevelX Value column.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tomerb]

I'm not sure I understad.

Is it not enough to have all the data as presented in the pbix file I shared?

In general, it seems like at times there are technical "tricks" that need to be applied in order to get something - which appears to be simple - to work correctly in Power BI.

Perhaps you can refer me to some documentation that explains the ideas behind applying these concepts?

[Anonymous]

Hi @tomerb ,

I added 3 columns to the DataTable corresponding to three levels of values.

Create the following measure to identify which level the current value is in.

Level = 
SWITCH(TRUE(),
ISINSCOPE(StaticTable[Level3]),"Level3",
ISINSCOPE(StaticTable[Level2]),"Level2",
ISINSCOPE(StaticTable[Level1]),"Level1",
ISINSCOPE(StaticTable[CategoryOrder]),"CategoryOrder",
"Unknown")

When it is added to the matrix, the corresponding level is shown in the value.

Modify the Running Total measure.

Running Total = 
VAR CurrentCategoryOrder = MAX('StaticTable'[CategoryOrder])
VAR filteredTB = FILTER(
        ALL('DataTable'[Category], 'DataTable'[CategoryOrder]),
        'DataTable'[CategoryOrder] <= CurrentCategoryOrder
    )
RETURN
SWITCH([Level],
"CategoryOrder",CALCULATE(SUM('DataTable'[Val]),filteredTB),
"Level1",CALCULATE(SUM('DataTable'[Level1 Value]),filteredTB),
"Level2",CALCULATE(SUM('DataTable'[Level2 Value]),filteredTB),
"Level3",CALCULATE(SUM('DataTable'[Level3 Value]),filteredTB),
CALCULATE(SUM('DataTable'[Val]),filteredTB)
)

Add the Running Total measure to the matrix. Now, for each level the corresponding Running Total value is displayed.

Please see the attahed pbix for reference.

Best Regards,
Dengliang Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.