waterfall based on many columns / DAX measures

[limarchi7]

Hello,

My data looks like the following, all are calculated DAX measures:

Before            Change 1            Change 2              Change 3                  After

10                        3                          2                            -1                          14

So in other words, if you add all values from the 3 change columns to the before value (10), you will end up with the after value (14). I would like to construct a waterfall to show before on the left, after on the right, and all 3 changes showing in the middle, but all of these are (DAX calculated) measures. Are there any waterfalls (basic or marketplace) that I can do this with? Normally I would change the structure in the backend, but that's not really an option here. So I either need to contruct a structure in PowerBI somehow (based on all measures), or use a waterfall that supports this.

[limarchi7]

hi @v-jingzhang 

thank you for the reply and sorry for the delay!

ideally the 3 changes would be part of the "breakdown" and the before and after would be in the category, but maybe I can hack it and work with this

in this case, the order is determined by the order you defined in the measure? 

[v-jingzhang]

Hi @limarchi7 

No, the order is determined by the table I added manually (the first image in my previous reply). "Before" should have the minimum index number (1 in the image) and "After" should have the maximum index number (5 in the image). Then sort the measure name column by index column. Sort one column by another column in Power BI

Best Regards,
Jing

[limarchi7]

Sorry for the delay. Thanks for this! It's not exactly the solution, but it's a very interesting hack, and gets it very close. If "after" is flipped to negative, it looks exactly like a waterfall needed, and if the label could be positive we would have it all. But I will accept the solution, as it gets it very close.  

[v-jingzhang · Accepted solution]

Hi @limarchi7 

You can add a table with all measure names and an index column like below. Sort the measure name column by the index column. 

Then create the following measure. 

Measure = SWITCH(SELECTEDVALUE('Table'[Stage]),"Before",[Before],"Change 1",[Change 1],"Change 2",[Change 2],"Change 3",[Change 3],"After",[After])

Use the measure name column on Category and use above measure on Y-axis of the waterfall chart. Sort it by measure name column ascendingly. Turn off the auto Total column. You can get the following result. Is this what you want? 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.