Power BI is a powerful data visualization tool, able to display vast amounts of data in easy-to-understand and attractive visuals. There are plenty of options to customise these visuals from colour, size, font types, line thickness, curve, and many more.
Despite this customisability, there are some limitations. The standard Power BI waterfall chart leaves much to be desired.
Our clients often want complete control of their visuals to help them find the data they are looking for. The above chart just won’t cut it.
Our goals are to:
- Create a waterfall chart to show the opening balance of the first week, the weekly change, and the closing balance of the last week.
- The chart must also dynamically update based on a between-slicer
Requirements
- Your fact table must aggregated weekly with the opening and closing balances for each week on the same line.
- You must have a copy of Simple Waterfall by DATANOMY LIMITED.
- A cup of coffee!
Step 1 Create a Dimension Index Table
In order to display your desired breakdowns you need to create an index table. This is an island table with 3 columns named “Dimension”, “Order”, and “Week_No”.
Dimension – The first column is a text column that starts with “OB” (for opening balance), numbers “1” to “53” and then “CB” (for closing balance).
Order – The second column is an index column starting with 1. We will use this column to sort our index.
Week_No – Lastly, we create another index column starting with 0. This is so that the numbers in our first column have numeric counterparts.
Step 2 Create a slicer table.
This is another island table with one column named Week_Slicer. It’s just a numeric column with numbers ranging from 1 to 53. This will be the column we use for our slicer.
Once you created your two island tables your model should look like this (note there are no relationships linking these table to eachother):
Step 3 Create an Opening Balance Measure.
Now for my favorite part, the DAX! We need to create a measure that will show us the opening balance of the first number in our slicer. So if we select week 3 as our start week, we want to see the opening balance of week 3. Here is our measure:
WF_Opening Balance =
CALCULATE(
SUM(‘Fact Table'[Opening Balance]),
‘Fact Table'[Week] = MIN(Custom_Waterfall_Index_Slicer[Week_Slicer])
)
Step 4 Create a Closing Balance Measure.
This measure is just the inverse of the above:
WF_Closing Balance =
CALCULATE(
SUM(‘Fact Table'[Closing Bank Balance]),
‘Fact Table'[Week] = MAX(Custom_Waterfall_Index_Slicer[Week_Slicer])
)
Step 5 Create a measure that will display the changes by week.
This measure took some head-scratching. When we first implemented this measure the slicer would not work. The reason was, the slicer was not affecting any of our tables. We created a relationship to remedy this but we noticed we were filtering out the OB and CB rows.
The way around this was to remove the relationship and just use DAX. Notice the IF statement filtered out changes between the MIN and MAX of our slicer:
WF_Week Change =
IF(
MAX(Custom_Waterfall_Index[Week_No]) <= MAX(Custom_Waterfall_Index_Slicer[Week_Slicer]) &&
MIN(Custom_Waterfall_Index[Week_No]) >= MIN(Custom_Waterfall_Index_Slicer[Week_Slicer]),
CALCULATE(
SUM(‘Fact Table'[Closing Bank Balance]) – SUM(‘Fact Table'[Opening Balance]),
‘Fact Table'[week] = MAX(Custom_Waterfall_Index[Week_No])
),
BLANK()
)
Now that we have our opening balance, closing balance, and weekly changes we can focus on plotting them on our waterfall chart.
Step 6 Create a Switch Statement to plot your measures.
Remember that dimension column we created earlier? We need to tell our measures to display based on the values in that column. For that, we use a switch statement:
WF Weekly Swtich Measure =
IF(HASONEVALUE(Custom_Waterfall_Index[Dimension]),
SWITCH(
VALUES(Custom_Waterfall_Index[Dimension]),
“OB”, [WF_Opening Balance],
“1”, [WF_Week Change],
“2”, [WF_Week Change],
“3”, [WF_Week Change],
“4”, [WF_Week Change],
“5”, [WF_Week Change],
“6”, [WF_Week Change],
“7”, [WF_Week Change],
“8”, [WF_Week Change],
“9”, [WF_Week Change],
“10”, [WF_Week Change],
“11”, [WF_Week Change],
“12”, [WF_Week Change],
“13”, [WF_Week Change],
“14”, [WF_Week Change],
“15”, [WF_Week Change],
“16”, [WF_Week Change],
“17”, [WF_Week Change],
“18”, [WF_Week Change],
“19”, [WF_Week Change],
“20”, [WF_Week Change],
“21”, [WF_Week Change],
“22”, [WF_Week Change],
“23”, [WF_Week Change],
“24”, [WF_Week Change],
“25”, [WF_Week Change],
“26”, [WF_Week Change],
“27”, [WF_Week Change],
“28”, [WF_Week Change],
“29”, [WF_Week Change],
“30”, [WF_Week Change],
“31”, [WF_Week Change],
“32”, [WF_Week Change],
“33”, [WF_Week Change],
“34”, [WF_Week Change],
“35”, [WF_Week Change],
“36”, [WF_Week Change],
“37”, [WF_Week Change],
“38”, [WF_Week Change],
“39”, [WF_Week Change],
“40”, [WF_Week Change],
“41”, [WF_Week Change],
“42”, [WF_Week Change],
“43”, [WF_Week Change],
“44”, [WF_Week Change],
“45”, [WF_Week Change],
“46”, [WF_Week Change],
“47”, [WF_Week Change],
“48”, [WF_Week Change],
“49”, [WF_Week Change],
“50”, [WF_Week Change],
“51”, [WF_Week Change],
“52”, [WF_Week Change],
“53”, [WF_Week Change],
“CB”, [WF_Closing Balance]
)
)
I’m sure we could use an Else statement for the week’s numbers but I like to be sure that we are displaying the information we need.
Step 7 Populate the waterfall chart.
On the canvas add a slicer with the Week_Slicer column from the Slicer Table.
Add the Simple Waterfall Chart with the dimension from the Index table and the switch statement.
In the settings of the Simple Waterfall Chart search for “Define Pillars” and tick both OB and CB:
Your Waterfall Chart should now look something like this:
Notice that when you adjust the slicer the OB and CB adjust accordingly:
We had a lot of fun building this report and learned a lot about DAX in the process. Better still, the client was very pleased with the result.
Did this tutorial help you? Do you need assistance with your Power BI reports? Get in touch with us today and let us help you uncover insights in your data!