Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.