Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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:
Solved! Go to Solution.
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.
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.
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 )
User | Count |
---|---|
21 | |
20 | |
14 | |
10 | |
8 |
User | Count |
---|---|
30 | |
28 | |
13 | |
12 | |
11 |