Waterfall variation by sales type

[goyasor]

Hi,

I'm trying to create a waterfall that shows:

First Bar:  2015 total

Middle Bars:  Year over Year Change

End Bar:  2016 Total 

For example:

First Bar:  2015 total  = 20M Cars

Middle Bars:  GM+400K, Ford -100K, Toyota-200K, Honda+500K, Chrysler+400K

End Bar:  2016 total = 21M Cars

I'm having trouble creating this. Does anyone know how I could achieve what I'm after? 

Many thanks.

[v-yulgu-msft]

Hi @goyasor,

By default, the waterfall chart can only show total value at the end bar, this value is calculated automatically.

Please post some sample data in table view , and if poossible post an image to describe your desired output so that I can try some workarounds to see whether your requirement can be achieved.

Best regards,
Yuliana Gu

[goyasor]

Thank you for your response. Here's a picture of what I've trying to achieve in Power BI. As you can see, it's possible in Excel with some data manipulation:

 

Essentially, I want the first column to be 2015 total and the last column to be 2016 total. The columns in-between should be the variance by category.

I can't find a way of implementing this Power BI. I hope you guys can help.

[MFelix]

Hi @goyasor,

I have made something similar to a post a few months ago, please check it out and if you have any questions please tell me.

https://community.powerbi.com/t5/Desktop/Waterfall-chart-adding-percentages-up-in-total-column/m-p/1...

The final result was the one show below, this example is with periods but can be change to Manufacturer.

Regards,

MFelix

[goyasor]

Thanks for your response. Unfortunately that didn't work for me.

I'm getting a constant figure in the Periods column:

[MFelix]

Be aware that this is a measure an not a calculated column that I use to show the percentages.

If you can share some data I can try to simulate and redefine the formula.

MFelix

[goyasor]

Here's the raw data I used in Excel:

Manufactuter	Category	2015 Sales	2016 Sales	Variance 2015-16
GM	Country A	1,000,000	2,000,000	1,000,000
GM	Country B	1,000,000	2,400,000	1,400,000
Ford	Country A	3,000,000	1,900,000	-1,100,000
Ford	Country B	4,000,000	3,000,000	-1,000,000
Toyota	Country A	1,000,000	2,000,000	1,000,000
Toyota	Country B	3,000,000	3,800,000	800,000
Honda	Country A	2,000,000	1,000,000	-1,000,000
Honda	Country B	2,500,000	2,000,000	-500,000
Chrysler	Country A	500,000	900,000	400,000
Chrysler	Country B	1,000,000	2,000,000	1,000,000
TOTAL		19,000,000	21,000,000

I used it to create this summary table (also in Excel):

2015 Sales	19,000,000
GM	2,400,000
Ford	-2,100,000
Toyota	1,800,000
Honda	-1,500,000
Chrysler	1,400,000
2016 Sales	21,000,000

Then finally the Waterfall chart:

[Vvelarde]

@goyasor

Hi, One Way to obtain this :

Create a New Table (Modeling Menu)

Table =
UNION (
    SUMMARIZECOLUMNS (
        Table1[Manufacturer],
        "Variation", SUM ( Table1[2016 Sales] ) - SUM ( Table1[2015 Sales] )
    ),
    ROW ( "Manufacturer,; "2015 Sales", "Variation", SUM ( Table1[2015 Sales] ) )
)

After That, Use the Waterfall Chart with the fields of the new table

[decshz]

Hello @Vvelarde could you share the pbix template? im trying to simulate what you did because this solution will help me to extrapolate it to another problem that I have, but I'm getting errors in the formula.

Table =
UNION (
    SUMMARIZECOLUMNS (
        Table1[Manufacturer],
        "Variation", SUM ( Table1[2016 Sales] ) - SUM ( Table1[2015 Sales] )
    ),
    ROW ( "Manufacturer,; "2015 Sales", "Variation", SUM ( Table1[2015 Sales] ) )
)

[goyasor]

Thank you for your response. Here's a picture of what I've trying to achieve in Power BI. As you can see, it's possible in Excel with some data manipulation:

 

Essentially, I want the first column to be 2015 total and the last column to be 2016 total. The columns in-between should be the variance by category.

I can't find a way of implementing this Power BI. I hope you guys can help.

[goyasor]

Thank you for your response. Here's a picture of what I've trying to achieve in Power BI. As you can see, it's possible in Excel with some data manipulation:

 

Essentially, I want the first column to be 2015 total and the last column to be 2016 total. The columns in-between should be the variance by category.

I can't find a way of implementing this Power BI. I hope you guys can help.