Running Total Calculation Across Multiple Categories

[Praj8050]

I am currently calculating the running total of units for Category_A using the following formula, but the result is not as expected. When I remove the date filters, the calculation works correctly. However, I need the formula to return accurate results for different date range selections.

Additionally, I would like to include Category_B in the running total calculation, with Category_A in rows, Category_B in columns, and Units in the values section.

Here is the formula I’ve used (created using Quick Measures):

Units running total =
CALCULATE(
SUM(df[Units]),
FILTER(
CALCULATETABLE(
SUMMARIZE(
df,
df[Sort (Category_A)],
df[Category_A]
),
ALLSELECTED(df)
),
ISONORAFTER(
df[Sort (Category_A)], MAX(df[Sort (Category_A)]), DESC,
df[Category_A], MAX(df[Category_A]), DESC
)
)
)


I attempted to modify the formula as discussed, but the results remain inaccurate. Could you please provide guidance or assistance in making the necessary adjustments to achieve the desired outcome?

[Praj8050]

@Sahir_Maharaj  : I implemented the formula you provided, but it's still not producing the correct running total.

[Sahir_Maharaj]

Hello @Praj8050,

Can you please try the following:

Units Running Total =
CALCULATE(
    SUM(df[Units]),
    FILTER(
        ALLSELECTED(df),  -- Ensures that date range selection is respected
        df[Sort (Category_A)] <= MAX(df[Sort (Category_A)]) &&  -- Running total up to the current row in Category_A
        df[Category_A] = MAX(df[Category_A]) &&  -- Maintain context for Category_A
        df[Category_B] = MAX(df[Category_B])  -- Maintain context for Category_B in columns
    )
)