Forum Discussion
Waterfall Chart based on Measures
I'm attempting to create a Year on Year waterfall chart which is dependant on the month selected by the user (select month vs that of the same month in the prior year). This therefore relies on a number of measures. Therefore the standard waterfall chart doesn't as it reflects all the months between the start and end dates rather than just the two dates in question. I cannot use any waterfall charts from the app list as it is against company policy.
I was following the You Tube video posted by "KnowledgeBank" (https://youtu.be/XgfJN8kvbOo ). I have been able to get the adapted code to work except for the first and final bars. The chart seems to default by having the 1st bar being the greater of either the start or the end value, while the final bar is the lower of the two - so it is always decreasing. Therefore in a perdiod where the waterfall chart should increase (i.e. the starting value is lower than the end value), bar 1 is the ending value, while the starting value is reflected as the final bar.
Where is an example:
Example chart
(The 1st and final bars are in the wrong position)
The code relies on two static data tables - which are as follows:
Table 1: "Recon Platforms"
| Platforms | Platform Sort Order |
| Equilend | 1 |
| Loanet | 2 |
| Pirum | 3 |
| No Auto Recon | 4 |
Table 2: "Waterfall - YoY"
| Start/End | Start/End Sort Order |
| Prior Year | 1 |
| Selected Month | 2 |
I then create a measure which drives the waterfall chart:
Waterfall - Platform YoY =
// Code based off https://www.youtube.com/watch?v=XgfJN8kvbOo
VAR EquilendStart =
CALCULATE (
'Measures Table'[Loan Count - Same Month Prior Year],
'Oracle ME Loan Details'[Primary Recon Platform] = "Equilend"
)
VAR EquilendEnd =
CALCULATE (
'Measures Table'[Loan Count - Selected Month],
'Oracle ME Loan Details'[Primary Recon Platform] = "Equilend"
)
VAR PirumStart =
CALCULATE (
'Measures Table'[Loan Count - Same Month Prior Year],
'Oracle ME Loan Details'[Primary Recon Platform] = "Pirum"
)
VAR PirumEnd =
CALCULATE (
'Measures Table'[Loan Count - Selected Month],
'Oracle ME Loan Details'[Primary Recon Platform] = "Pirum"
)
VAR LoanetStart =
CALCULATE (
'Measures Table'[Loan Count - Same Month Prior Year],
'Oracle ME Loan Details'[Primary Recon Platform] = "Loanet"
)
VAR LoanetEnd =
CALCULATE (
'Measures Table'[Loan Count - Selected Month],
'Oracle ME Loan Details'[Primary Recon Platform] = "Loanet"
)
VAR NoAutoRecStart =
CALCULATE (
'Measures Table'[Loan Count - Same Month Prior Year],
'Oracle ME Loan Details'[Primary Recon Platform] = "No Auto Recon"
)
VAR NoAutoRecEnd =
CALCULATE (
'Measures Table'[Loan Count - Selected Month],
'Oracle ME Loan Details'[Primary Recon Platform] = "No Auto Recon"
)
VAR NeedToDefineStart =
CALCULATE (
'Measures Table'[Loan Count - Same Month Prior Year],
'Oracle ME Loan Details'[Primary Recon Platform] = "Need to define"
)
VAR NeedToDefineEnd =
CALCULATE (
'Measures Table'[Loan Count - Selected Month],
'Oracle ME Loan Details'[Primary Recon Platform] = "Need to define"
)
RETURN
SWITCH (
SELECTEDVALUE ( 'Waterfall - YoY'[Start/End Sort Order] ), 1,
SWITCH (
SELECTEDVALUE ( 'Recon Platforms'[Platforms] ),
"Equilend", EquilendStart,
"Loanet", LoanetStart,
"Pirum", PirumStart,
"No Auto Recon", NoAutoRecStart + NeedToDefineStart,
[Loan Count - Same Month Prior Year]
),
2,
SWITCH (
SELECTEDVALUE ( 'Recon Platforms'[Platforms] ),
"Equilend", EquilendEnd,
"Loanet", LoanetEnd,
"Pirum", PirumEnd,
"No Auto Recon", NoAutoRecEnd + NeedToDefineEnd,
[Loan Count - Selected Month]
)
)
The chart is then defined as follows:
I'm using Power BI Version 2.95.983.0 - which is the latest internally release version available to me.
What do I need to do in order to get the 1st and final bars to appear in the correct order (for both increasing and decreasing trends)?
Thanks
1 Reply
- v-yalanwu-msftCommunity Support
Hi, G_Whit-UK
I'm sorry I can't restore your model and data. According to your description, maybe you can try to create a sorted column and use Sort one column by another column.
If not right ,can you share me a simple file after removing sensitive information?
Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.