Forum Discussion
Running Total with exclusions
Hi all,
I am trying to recreate a chart that the business uses currently in Excel, that I would like to move to Power BI. It is like a waterfall chart, but has Targets included in the chart as well (Budget and Forecast) like this:
to do this we have a table like this:
| Category | CV €M | Index | CV €M 2 | Base 2 | Base 3 |
| Q1Sold Churn | 1.83 | 0 | 1.83 | 0.00 | 0.00 |
| Q1 Unsold Churn | 4.98 | 1 | 4.98 | 1.83 | 1.83 |
| Q1 Sold Medium | 1.34 | 2 | 1.34 | 6.81 | 6.81 |
| Q1 Unsold Medium | 3.59 | 3 | 3.59 | 8.15 | 8.15 |
| Fcst Q1 | 25.22 | 4 | 0.00 | 11.74 | 0.00 |
| Bud Q1 | 25.04 | 5 | 0.00 | 11.74 | 0.00 |
| Q2 Sold Churn | 1.83 | 6 | 1.83 | 11.74 | 11.74 |
| Q2 Unsold Churn | 4.98 | 7 | 4.98 | 13.57 | 13.57 |
| Q2 Sold Medium | 1.34 | 8 | 1.34 | 18.55 | 18.55 |
| Q2 Unsold Medium | 3.59 | 9 | 3.59 | 19.89 | 19.89 |
| Deal 1 | 8.20 | 10 | 8.20 | 23.48 | 23.48 |
| Deal 2 | 6.20 | 11 | 6.20 | 31.68 | 31.68 |
| Deal 3 | 5.30 | 12 | 5.30 | 37.88 | 37.88 |
| H1 YTD Fcst | 56.09 | 13 | 0.00 | 43.18 | 0.00 |
| H1 YTD Bud | 70.14 | 14 | 0.00 | 43.18 | 0.00 |
| Q3 Unsold Churn | 6.81 | 15 | 6.81 | 43.18 | 43.18 |
| Q3 Unsold Medium | 4.93 | 16 | 4.93 | 49.99 | 49.99 |
| Deal 4 | 17.30 | 17 | 17.30 | 54.92 | 54.92 |
| Deal 5 | 4.60 | 18 | 4.60 | 72.22 | 72.22 |
| Q3 YTD Fcst | 99.27 | 19 | 0.00 | 76.82 | 0.00 |
| Q3 YTD Bud | 98.23 | 20 | 0.00 | 76.82 | 0.00 |
| Q4 Unsold Churn | 6.81 | 21 | 6.81 | 76.82 | 76.82 |
| Q4 Unsold Medium | 4.93 | 22 | 4.93 | 83.63 | 83.63 |
| Deal 6 | 5.60 | 23 | 5.60 | 88.56 | 88.56 |
| Deal 7 | 1.90 | 24 | 1.90 | 94.16 | 94.16 |
| FY YTD Fcst | 145.44 | 25 | 0.00 | 96.06 | 0.00 |
| FY YTD Bud | 145.44 | 26 | 0.00 | 96.06 | 0.00 |
The first 3 columns (Category, CV €M and Index) are the data table that we get from another system, and the last 3 columns are calculated:
CV €M 2 is simply the CV €M column, but Zero when the Category contains "Bud" or "Fcst"
Base 2 is the running total of CV €M 2 but offset by 1 row
Base 3 is Base 2, but Zero when the Category contains "Bud" or "Fcst"
We then use CV €M and Base 3 for the chart data
I am trying to calculate measures in DAX (Not M) to replicate these columns, and I can get to a running total:
Hi Anonymous ,
Here is the DAX of these columns:
CV €M 2 = IF ( CONTAINSSTRING ( [Category], "Fcst" ) || CONTAINSSTRING ( [Category], "Bud" ), 0, [CV €M] ) Base 2 = VAR _a = CALCULATE ( SUM ( Table1[CV €M 2] ), FILTER ( Table1, [Index] < EARLIER ( [Index] ) ) ) RETURN IF ( ISBLANK ( _a ), 0, _a ) Base 3 = IF ( CONTAINSSTRING ( [Category], "Fcst" ) || CONTAINSSTRING ( [Category], "Bud" ), 0, [Base 2] )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
- tamerj1
Community Champion
Hi Anonymous
please try
Cumulative Base = VAR SelectedData = ALLSELECTED ( 'FY Chart Data' ) VAR CurrentRank = MAX ( 'FY Chart Data'[Rank] ) VAR PreviousData = FILTER ( SelectedData, 'FY Chart Data'[Rank] <= CurrentRank && NOT ( 'FY Chart Data'[category] IN { "Bud", "Fcst" } ) ) RETURN CALCULATE ( [CV €M], PreviousData ) 
v-jianboli-msftCommunity Support
Hi Anonymous ,
Here is the DAX of these columns:
CV €M 2 = IF ( CONTAINSSTRING ( [Category], "Fcst" ) || CONTAINSSTRING ( [Category], "Bud" ), 0, [CV €M] ) Base 2 = VAR _a = CALCULATE ( SUM ( Table1[CV €M 2] ), FILTER ( Table1, [Index] < EARLIER ( [Index] ) ) ) RETURN IF ( ISBLANK ( _a ), 0, _a ) Base 3 = IF ( CONTAINSSTRING ( [Category], "Fcst" ) || CONTAINSSTRING ( [Category], "Bud" ), 0, [Base 2] )Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
