Forum Discussion
Solved
Running total with IF statement?
Hi guys,
Struggeling with a running total. I only want a running total if slicer selection is the same as a column in my table.
For example:
Slicer = FISCALYEAR
Then I only want a running total of all values/fiscalyears where column DP_YEAR is the same als the slicer(FISCALYEAR).
| ASSETTRANS | FISCALYEAR | ASSET_ID | AMOUNT | DP_YEAR |
| CAP | 2020 | 11287 | -6799111 | 2020 |
| ADD | 2020 | 11293 | -13081,7 | 2021 |
| ADD | 2021 | 11293 | -1774,94 | 2021 |
| ADD | 2020 | 11293 | -197763 | 2021 |
| ADD | 2020 | 11293 | -1234,77 | 2021 |
| ADD | 2020 | 11293 | -593,94 | 2021 |
| ADD | 2020 | 11293 | -35532,5 | 2021 |
| ADD | 2020 | 11293 | -5493,17 | 2021 |
| CAP | 2020 | 11287 | 20059,29 | 2020 |
| CAP | 2020 | 11287 | -40118,6 | 2020 |
| CAP | 2020 | 11287 | -19418 | 2020 |
| CAP | 2020 | 11287 | -49582,9 | 2020 |
| CAP | 2020 | 11287 | -52732 | 2020 |
| CAP | 2020 | 11287 | -94165 | 2020 |
| CAP | 2020 | 11287 | -74781 | 2020 |
| CAP | 2020 | 11287 | -14009,2 | 2020 |
| CAP | 2020 | 11287 | -168717 | 2020 |
| CAP | 2020 | 11287 | -145684 | 2020 |
| CAP | 2020 | 11287 | -399640 | 2020 |
| CAP | 2020 | 11287 | -353831 | 2020 |
| CAP | 2021 | 11293 | -53992,3 | 2021 |
ASSET_ID 11287 returns IF slicer FISCALYEAR = 2020
| -8191731 |
and when FISCALYEAR = 2021 then 0
ASSET_ID 11293 returns IF slicer FISCALYEAR = 2020
0
and when FISCALYEAR = 2021 then
| -309466 |
Thank you for you help.
- Thank you for your reply, the following DAX solved my problem.RT_DP_YEAR =VAR FISCALYEAR = SELECTEDVALUE('table'[FISCALYEAR])RETURNCALCULATE(SUMX('table','table'[AMOUNT]),FISCALYEAR = 'table'[DP_YEAR],ALL('table'[FISCALYEAR]))
2 Replies
Running total needs something that it can sort by. it is not clear from your data which column is suitable for sorting. Please elaborate.
- Thank you for your reply, the following DAX solved my problem.RT_DP_YEAR =VAR FISCALYEAR = SELECTEDVALUE('table'[FISCALYEAR])RETURNCALCULATE(SUMX('table','table'[AMOUNT]),FISCALYEAR = 'table'[DP_YEAR],ALL('table'[FISCALYEAR]))
