cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sparkeltje
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). 

 

ASSETTRANSFISCALYEARASSET_IDAMOUNTDP_YEAR
CAP202011287-67991112020
ADD202011293-13081,72021
ADD202111293-1774,942021
ADD202011293-1977632021
ADD202011293-1234,772021
ADD202011293-593,942021
ADD202011293-35532,52021
ADD202011293-5493,172021
CAP20201128720059,292020
CAP202011287-40118,62020
CAP202011287-194182020
CAP202011287-49582,92020
CAP202011287-527322020
CAP202011287-941652020
CAP202011287-747812020
CAP202011287-14009,22020
CAP202011287-1687172020
CAP202011287-1456842020
CAP202011287-3996402020
CAP202011287-3538312020
CAP202111293-53992,32021

 

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

Thank you for your reply, the following DAX solved my problem. 
 
RT_DP_YEAR =

 

VAR FISCALYEAR = SELECTEDVALUE('table'[FISCALYEAR])
 
RETURN
CALCULATE(SUMX('table',
 'table'[AMOUNT]),
 
FISCALYEAR = 'table'[DP_YEAR],
ALL('table'[FISCALYEAR]))

View solution in original post

2 REPLIES 2
lbendlin
Super User
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.

Thank you for your reply, the following DAX solved my problem. 
 
RT_DP_YEAR =

 

VAR FISCALYEAR = SELECTEDVALUE('table'[FISCALYEAR])
 
RETURN
CALCULATE(SUMX('table',
 'table'[AMOUNT]),
 
FISCALYEAR = 'table'[DP_YEAR],
ALL('table'[FISCALYEAR]))

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors