Forum Discussion
Cumulative sum with filter giving wrong result
Hello Community,
I have a requirement where I need to Calculate the Cummulative from a Column named Actual.
I have the actual values for multiple sites for every month of Current Fiscal Year (April 2023 - March 2024)
One of the Site is Hornell as you can see and It has the value as 123 which is the sum of 5 different works (25+10+31+29+28).
And The below is the formula I used for Cummulative Actual.
Cumulative_Actual =
CALCULATE (
SUM ( 'TC-Forecast Road Map'[Actual]),
FILTER (
ALL ( 'TC-Forecast Road Map' ),
'TC-Forecast Road Map'[Year Month] <= MAX ( 'TC-Forecast Road Map'[Year Month] )
)
)
but when I see the Cummulative value for the Month of April It's giving 129, which is the Combination of Hornell and One more site.
The cummulative actual is correct when filter is not applied. But when the site filter is applied it is giving wrong output (129 instead of 123).
Please help me with this issue.
Thanks 😊
The other site is appearing because you tell it to ignore the site filter with
ALL ( 'TC-Forecast Road Map' ),The simplest fix is to simply specify that you need All year-Month values per site - so something like:
ALL ( 'TC-Forecast Road Map'[Year Month] ),
1 Reply
- vicky_
Super User
The other site is appearing because you tell it to ignore the site filter with
ALL ( 'TC-Forecast Road Map' ),The simplest fix is to simply specify that you need All year-Month values per site - so something like:
ALL ( 'TC-Forecast Road Map'[Year Month] ),