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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Burcu
New Member

Running total with If condition

Hi all,

I'm new in power BI and I'm trying to change excel reports to Power BI.

I have a table in excel like this

DateDaily Total USDUSD RunnigTotal
31.12.20170,000,00
1.01.201820.000,0020.000,00
2.01.201845.000,0065.000,00
3.01.2018-65.000,000,00
4.01.201818.000,0018.000,00
5.01.201896.000,00114.000,00
6.01.2018-114.000,000,00
7.01.201814.870,0014.870,00
8.01.20183.540,0018.410,00
9.01.201858.300,0076.710,00
10.01.201832.810,00109.520,00
11.01.20184.500,00114.020,00

In excel my running total column formul is =IF(C3>0;(C3+B4);B4). I'm tring to convert it to power BI but I couldn't get success.

In power BI, I get my first 2 columns as combine table then I create a measurement for running total column and apply the below formula

USD RUNNING TOTAL = IF(CALCULATE(SUM(COMBINE[Daily Total USD] );FILTER (ALL ( COMBINE[Date] );                                                                           COMBINE[Date] <= MAX ( COMBINE[Date]))) > SUM (COMBINE[Daily Total USD]);                                      CALCULATE(SUM(COMBINE[Daily Total USD] );FILTER (ALL ( COMBINE[Date] );                                                                            COMBINE[Date] <= MAX ( COMBINE[Date])));SUM (COMBINE[Daily Total USD]))

 

here is power BI measurement result. Normally, it calculate running total till 0 point. after 0 point it get the sum of [daily total USD]

DateDaily Total USDUSD RunnigTotal
31.12.20170,000,00
1.01.201820.000,0020.000,00
2.01.201845.000,0065.000,00
3.01.2018-65.000,000,00
4.01.201818.000,0018.000,00
5.01.201896.000,009.600,00
6.01.2018-114.000,00114.000,00
7.01.201814.870,0014.870,00
8.01.20183.540,003.540,00
9.01.201858.300,0058.300,00
10.01.201832.810,0032.810,00
11.01.20184.500,004.500,00

 

Does anyone have any idea of how can I get the same result for [running total usd] column with the excel?

3 REPLIES 3
FlaF
Helper I
Helper I

You can calculate new column like if(table.column 3>0;table.column 3+table.column 2;table.column 2)

 

Ciao

Flavia

Zubair_Muhammad
Community Champion
Community Champion

@Burcu

 

Try this calculated column

 

Calculated Column =
CALCULATE (
    SUM ( COMBINE[Daily Total USD] ),
    FILTER ( COMBINE, COMBINE[Date] <= EARLIER ( COMBINE[Date] ) )
)

Regards
Zubair

Please try my custom visuals

Hi @Zubair_Muhammad,

Thank you for your reply. It almost solved my problem. There is a little problem when the running total turn negative.

when calculation result turns the negative it would continue and start to get total on your own row. Do you have suggestions for that?

PS. I want to reach the same result with USD Calculated in Excel column.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.