Convert table structure for waterfall chart

[Nata800]

Dear all,

I have following table structure with revenues for 2022 and 2023 as well as delta revenues 2023 vs. 2022 per Country.

Category	Breakdown	Revenue
2022	2022	100
Delta	Country x	20
Delta	Country y	20
Delta	Country z	60
2023	2023	200

As these are different value types (base and delta values) the standard waterfall chart is not working out. This is the desired outcome shown via ThinkCell, the datasheet logic follows exactly the one of the table

How can I transform the table structure to suit the best for standards waterfall charts?

Thanks so much!

Regards

Nata

[v-cgao-msft]

Hi @Nata800 ,

Remove the bottom row in Power Query and add [Index] column to help sort.

The only difference with the image you gave is that 'total' is used here instead of '2023'.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Nata800]

Thanks Gao for your solution and sorry for the delay (was on vacation).

I think your solution will work, but I would need to enrich the table slightly. In my example table above the country variances are completely summing up to the delta of 2023 vs. 2022. However in reality my table does not, which means there is always a difference

So before I can use your Total column (to reflect the value of 2023) I would need to add rows with "Country Others" for the deltas within a Region (A-D). With what formula could this be achievable in Power Query?

Thanks a lot!

Regards
Nata

[Nata800]

Hi Pete,

thanks for your quick response!

My issue is that I only have the variance values ​​available and not the initial revenues for 2022/2023 per country.  Could there be another solution with creating a new measure (read about the swith function, but wasn't sure if it's applicable to my issue).

Thanks!

Regards
Nata

[BA_Pete]

Hi Nata,

I think the correct structure within Power Query would be this:

year	breakdown	revenue
2022	X	40
2022	Y	40
2022	Z	20
2023	X	20
2023	Y	20
2023	Z	60

You would then use the Waterfall visual with [year] in Category, [breakdown] in Breakdown, and [revenue] in Y-axis.

Pete