Forum Discussion
How to exclude Grand Total from Running Total calculation?
Hello, I am trying to create a Running Total measure. I have succeeded in doing so with the following code:
Running Total =
CALCULATE(
SUM('Sales'[Profit]),
FILTER(
ALL('Calendar'[Year]),
'Calendar'[Year] <= MAX('Calendar'[Year])
)
)
However, in my 'Sales' table there is a row named 'Grand Total' that is being calculated with all the individual [Profit] values and incorrectly driving up the Running Total. How can I update the above code to filter out the 'Grand Total' row?
Hi Anonymous ,
Please try this.
Running Total = CALCULATE ( SUM ( 'Sales'[Profit] ), FILTER ( ALL ( 'Calendar'[Year] ), 'Calendar'[Year] <= MAX ( 'Calendar'[Year] ) ), FILTER ( ALL ( 'Sales' ), 'Sales'[Filed Name] <> "Grand Total" ) )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data
2 Replies
Hi Anonymous ,
Please try this.
Running Total = CALCULATE ( SUM ( 'Sales'[Profit] ), FILTER ( ALL ( 'Calendar'[Year] ), 'Calendar'[Year] <= MAX ( 'Calendar'[Year] ) ), FILTER ( ALL ( 'Sales' ), 'Sales'[Filed Name] <> "Grand Total" ) )Best Regards,
Gao
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
How to get your questions answered quickly -- How to provide sample data