Forum Discussion
Need Assistance with Waterfall Chart
Hello! I've been using Power BI for a little under a year and this is my first attempt at creating a waterfall chart. I'm looking to visualize year-over-year changes to the total cFTE where Year End cFTE would act as 'Total' columns and Joined/Left cFTEs would be the additions and subtractions between years. Below is some sample data showing how I formatted my query to use (rosters are going to be combined, just wanted it as a slicer), and how it was done in Excel before I got tasked with automating this refresh process.
| Roster | As of | Start cFTE | Joined cFTE | Left cFTE | Year End cFTE |
| XX | FY21 | 21 | 9 | -6 | 24 |
| XX | FY22 | 21 | 8 | -7 | 25 |
| XX | FY23 | 21 | 11 | -5 | 31 |
| XX | FY24 | 21 | 6 | -8 | 29 |
| ZZ | FY21 | 32 | 13 | -10 | 35 |
| ZZ | FY22 | 32 | 10 | -4 | 41 |
| ZZ | FY23 | 32 | 10 | -9 | 42 |
| ZZ | FY24 | 32 | 15 | -5 | 52 |
I've been warned that the default waterfall chart in Power BI isn't the greatest, and even tried messing around with the free version of Innofalls from the marketplace, but I can't seem to get it to do what I'm hoping for. Do I need to format my data differently? I'm also open to any other suggestions for visuals that may be good here, so thank you very much in advance!
Hi CJS1199
You can refer to the following solution.
1.You need to unpivot your table to the following format in power query, click Roster and As of column, then selet unpivot other columns.
it will be the follwing format.
2.Then in power bi desktop , create a calcularted column.
Column = [As of]&"-"&[Attribute]3.in visual store, select the simple waterfall.
4.Then put the following field to the visual.
5.Then in Define Pillars, select the end cfte of each year.
6.Then select sort by column and descending.
Output
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Sorry in advance, not sure why the table I provided isn't formatting properly...
Hi CJS1199
You can refer to the following solution.
1.You need to unpivot your table to the following format in power query, click Roster and As of column, then selet unpivot other columns.
it will be the follwing format.
2.Then in power bi desktop , create a calcularted column.
Column = [As of]&"-"&[Attribute]3.in visual store, select the simple waterfall.
4.Then put the following field to the visual.
5.Then in Define Pillars, select the end cfte of each year.
6.Then select sort by column and descending.
Output
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you very much, I think this should work perfectly! I am going to give this a go today and I'll report back with any issues. I like how this visual from the store lets you define the pillars you want as a 'total' like you can do in the Excel version.
