Forum Discussion

Praj8050's avatar
Praj8050
Helper I
1 year ago

Running Total Calculation Across Multiple Categories

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?

Need Help
Tips and Tricks
Tutorial Requests

2 Replies

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    1 year ago

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