cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
Frequent Visitor

RT_DP_YEAR =

VAR FISCALYEAR = SELECTEDVALUE('table'[FISCALYEAR])

RETURN
CALCULATE(SUMX('table',
'table'[AMOUNT]),

FISCALYEAR = 'table'[DP_YEAR],
ALL('table'[FISCALYEAR]))
2 REPLIES 2
Super User

Running total needs something that it can sort by.  it is not clear from your data which column is suitable for sorting. Please elaborate.

Frequent Visitor

RT_DP_YEAR =

VAR FISCALYEAR = SELECTEDVALUE('table'[FISCALYEAR])

RETURN
CALCULATE(SUMX('table',
'table'[AMOUNT]),

FISCALYEAR = 'table'[DP_YEAR],
ALL('table'[FISCALYEAR]))

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors