Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Date | Daily Total USD | USD RunnigTotal |
31.12.2017 | 0,00 | 0,00 |
1.01.2018 | 20.000,00 | 20.000,00 |
2.01.2018 | 45.000,00 | 65.000,00 |
3.01.2018 | -65.000,00 | 0,00 |
4.01.2018 | 18.000,00 | 18.000,00 |
5.01.2018 | 96.000,00 | 114.000,00 |
6.01.2018 | -114.000,00 | 0,00 |
7.01.2018 | 14.870,00 | 14.870,00 |
8.01.2018 | 3.540,00 | 18.410,00 |
9.01.2018 | 58.300,00 | 76.710,00 |
10.01.2018 | 32.810,00 | 109.520,00 |
11.01.2018 | 4.500,00 | 114.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]
Date | Daily Total USD | USD RunnigTotal |
31.12.2017 | 0,00 | 0,00 |
1.01.2018 | 20.000,00 | 20.000,00 |
2.01.2018 | 45.000,00 | 65.000,00 |
3.01.2018 | -65.000,00 | 0,00 |
4.01.2018 | 18.000,00 | 18.000,00 |
5.01.2018 | 96.000,00 | 9.600,00 |
6.01.2018 | -114.000,00 | 114.000,00 |
7.01.2018 | 14.870,00 | 14.870,00 |
8.01.2018 | 3.540,00 | 3.540,00 |
9.01.2018 | 58.300,00 | 58.300,00 |
10.01.2018 | 32.810,00 | 32.810,00 |
11.01.2018 | 4.500,00 | 4.500,00 |
Does anyone have any idea of how can I get the same result for [running total usd] column with the excel?
You can calculate new column like if(table.column 3>0;table.column 3+table.column 2;table.column 2)
Ciao
Flavia
Try this calculated column
Calculated Column = CALCULATE ( SUM ( COMBINE[Daily Total USD] ), FILTER ( COMBINE, COMBINE[Date] <= EARLIER ( COMBINE[Date] ) ) )
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |