Waterfall charts show start and end values by a measure

[qazwsxedc]

I want to show the start value, breakdown and end values by a waterfall chart. I found a post showing how to do that.

https://businessintelligist.com/2020/06/12/power-bi-dax-how-to-make-waterfall-charts-work-showing-st...

Although I don't really understand the measure in the post, I copied it and it works well either between Aug 2020 and Dec 2020 or Dec 2021 and Dec 2022 but if I want to show all data in one graph between Aug 2020 and Dec 2022, it doesn't work.

Is it possible to show all data in one graph between Aug 2020 and Dec 2022 using that measure? or it only works between two period? Thanks!

My measure: 

1a)Volume Variance =

var selectedDate = SELECTEDVALUE('Date'[Date])

var selectedBreakdown = SELECTEDVALUE(Breakdown[Breakdown])

return

SWITCH(selectedDate,

"Aug_2020",

SWITCH(selectedBreakdown,

"Amortization", -1*[Amort_2020],

"Maturities", -1*[Matu_2020],

"Prepayment", -1*[Prepay_2020],

"New Sales", -1*[New Sales_2020],

"Renewals",-1*[Renewal_2020],

[Volume_Aug_2020]

),

"Dec_2020",

SWITCH(selectedBreakdown,

"Amortization", 0,

"Maturities", 0,

"Prepayment", 0,

"New Sales", 0,

"Renewals", 0,

[Volume_Dec_2020]

),

"Dec_2021",

SWITCH(selectedBreakdown,

"Amortization", -1*[Amort_2022],

"Maturities", -1*[Matu_2022],

"Prepayment", -1*[Prepay_2022],

"New Sales", -1*[New Sales_2022],

"Renewals",-1*[Renewal_2022],

[Volume_Dec_2021]

),

"Dec_2022",

SWITCH(selectedBreakdown,

"Amortization", 0,

"Maturities", 0,

"Prepayment",0,

"New Sales", 0,

"Renewals",0,

[Volume_Dec_2022]

))

[v-easonf-msft]

Hi , @qazwsxedc 

It's possible to show all data in one graph between Aug 2020 and Dec 2022 .

Could you  please share  a sample filefor testing?

You  can  check  whether the solution in these related threads help.

https://community.powerbi.com/t5/Service/Waterfall-chart-with-the-data-of-the-selected-year-in-a-sli... 

https://community.powerbi.com/t5/Desktop/Waterfall-chart-start-valu/m-p/1020967

Best Regards,
Community Support Team _ Eason

[qazwsxedc]

Thank you Eason! I have worked around to show the start and end values by waterfall. However, can I change the order of the breakdown? E.g. I would like the order to be Volume, Mix, Gross Revenue and Discounts i/o ordering by the breakdown's value.

The related PBI file is uplaoded. Could you please take a look? Thanks!

https://drive.google.com/file/d/1PnVcjE-e1dGA0OnGvGOUCbQURlcNQ0rD/view?usp=sharing

[v-easonf-msft]

Hi, @qazwsxedc 

Sorry,after my research ,I'm  afraid  it doesn't support it currently.

Even you  drag the filed  "Breakdown sort"  to tooltip then sort by it , or  sort column "Breakdown" by  column "Breakdown sort" ,it will not affect the order of the breakdown in waterfall chart.

As of now, I couldn't find any workaroud.I would suggest to  create a support ticket to PowerBi Support Team.Maybe they will have better advice.

https://powerbi.microsoft.com/en-us/support/ 

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.