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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors
Users online (971)