PowerBI Waterfall - How to show Year over Year movements (with sum of Up & Down)

[nic2023]

I'm struggling using Waterfall visual on multiple year over Year movements with sum of Up & Down.

The excel spreadsheet is the raw data:

The desired visual should be - the total of project A to G: 

I did attempted to create new measures one by one:

Sum of FY25 Budget

Sum of FY25 Ups

Sum of FY25 down

Sum of FY25 Cost

Sum of FY26 Ups

Sum of FY26 down

Sum of FY26 Cost

However still unable to present the above measures' values with the waterfall columns. 

Thank you so much for your help in adance. 

***********************************************************

(Dated: 19/03/2025) Further updated with the desired visuals: 

[v-hashadapu]

Hi @nic2023 , Just checking in—were you able to resolve the issue?
If one of the replies helped, please consider marking it as "Accept as Solution" and giving a 'Kudos'. Doing so can assist other community members in finding answers more quickly.
Thank you!

[v-hashadapu]

Hi @nic2023 ,
I hope the information shared was helpful. If you have any additional questions or would like to explore the topic further, feel free to reach out. If any of the responses resolved your issue, please mark it "Accept as solution" and give it a 'Kudos' to support other members in the community.
Thank you!

[v-hashadapu]

Hi @nic2023 ,
I wanted to follow up and see if you’ve had a chance to review the information provided here.
If any of the responses helped solve your issue, please consider marking it "Accept as Solution" and giving it a 'Kudos' to help others easily find it.
Let me know if you have any further questions!

[Anonymous]

Hi @nic2023 ,

Any update on this? Can you please share some more detail information with expected results? They should help us clarify your scenario and test.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[nic2023]

Thanks for the message!

I've just edited and re-described the desired visual with more details. Thank you!

[danextian · Accepted solution]

Hi @nic2023 

There’s no out-of the box way to do this achieve this. It requires data manipulation in the query editor, a disconnected dimension table, and measures that reference both the fact table, related dimensions, and the disconnected dimension. While doable the approach isn’t particularly beginner-friendly. Please review the attached PBIX file.

[danextian]

Hi @nic2023 

Is the chart your expected result? If not, please post. Also, how are the ups and down calculated?

[nic2023]

I've just added additional details, hope that clarifies. Thank you! 

[rajendraongole1]

Hi @nic2023  - you can create the required measures Define the following measures: as per your sample shared>

FY25 Total Budget = SUM('Table'[FY25 Budget])
FY25 Total Cost = SUM('Table'[FY25 Cost])
FY26 Total Cost = SUM('Table'[FY26 Cost])

FY25 Up = SUMX('Table', IF('Table'[FY25 Cost] > 'Table'[FY25 Budget], 'Table'[FY25 Cost] - 'Table'[FY25 Budget], 0))
FY25 Down = SUMX('Table', IF('Table'[FY25 Cost] < 'Table'[FY25 Budget], 'Table'[FY25 Budget] - 'Table'[FY25 Cost], 0))

FY26 Up = SUMX('Table', IF('Table'[FY26 Cost] > 'Table'[FY25 Cost], 'Table'[FY26 Cost] - 'Table'[FY25 Cost], 0))
FY26 Down = SUMX('Table', IF('Table'[FY26 Cost] < 'Table'[FY25 Cost], 'Table'[FY25 Cost] - 'Table'[FY26 Cost], 0))

Create a Supporting Table for the Waterfall Chart Power BI’s Waterfall chart requires a category column for breakdown

Waterfall Categories =
DATATABLE(
"Category", STRING,
"Value", DOUBLE,
{
{"FY25 Budget", [FY25 Total Budget]},
{"Sum of Up", [FY25 Up]},
{"Sum of Down", -[FY25 Down]}, -- Negative to show downward movement
{"FY25 Cost", [FY25 Total Cost]},
{"Sum of Up", [FY26 Up]},
{"Sum of Down", -[FY26 Down]},
{"FY26 Cost", [FY26 Total Cost]}
}
)

Now drag the catgory from with waterfall categories and values.

Hope this helps.

[Deku]

Problem with this version is the budget amount are going to be cached on refresh and will not update when you slice or filter. 

You could do a similar approach but with field parameters and retain the ability to slice and dice. You add the the measures to a field parameter table and rename the columns as required.