How to make running total ignore other columns DAX

Question

I have a table in Power BI that calculates the running total. However, when I add additional columns to the table, the values get messed up. Any ideas on how get the running total to ignore specific columns?

Here is my code:

Measure =
CALCULATE (
    SUM ( Append1[AVAILABLE] ),
    FILTER (
        ALL ( Append1[DUE DATE] ),
        Append1[DUE DATE] <> MAX ( Append1[DUE DATE] )
    )
)
    + CALCULATE (
        SUM ( Append1[ORDER QTY] ),
        FILTER (
            ALL ( Append1[DUE DATE] ),
            Append1[DUE DATE] <= MAX ( Append1[DUE DATE] )
        )
    )
    - CALCULATE (
        SUM ( Append1[REQUIREMENT QTY] ),
        FILTER (
            ALL ( Append1[DUE DATE] ),
            Append1[DUE DATE] <= MAX ( Append1[DUE DATE] )
        )
    )

Below are pictures of what the table looks like when it runs correctly and what it looks like when I add another column and the values get messed up.

Correct running total:

Incorrect Running Total:

Thanks in advance for your help!

Use ALLEXCEPT to selectively ignore any but the selected filter context — Jon
I'm having trouble getting the syntax correct. Can you give me an example of how it should be written if I want to ignore the ID column in the above example? — Sky
And to be clear you don't mean using SUMX which ignores all filters, right? — Mark S.
What's going on in that first part of your measure... Append1[DUE DATE] <> MAX ( Append1[DUE DATE] ) ... do you really want it like that? I would expect a "<=" not a "<>" — Ryan B.
@Sky for performance purposes (1), bear in mind that "<" and ">" are complex operations that are translated into a sequence of comparison IN (val1, val2, ...). If your calendar goes from 2020 to 1900, using "<" would generate A LOT of comparison based on the cardinality of the field. Therefore, you might want to define it as NOT(Append1[DUE DATE] > MAX ( Append1[DUE DATE] ) — Seymour

Sky Sky · Accepted Answer · 2020-02-07T22:27:20

1

votes

I was able to get it working correctly using ALLSELECTED. Thanks for your help.

How to make running total ignore other columns DAX

1 Answers