cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
goyasor
Frequent Visitor

Waterfall variation by sales type

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.

10 REPLIES 10
Microsoft
Microsoft

Re: Waterfall variation by sales type

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
goyasor
Frequent Visitor

Re: Waterfall variation by sales type

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:

 

 image.png

 

 

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
Frequent Visitor

Re: Waterfall variation by sales type

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:

 

 image.png

 

 

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
Frequent Visitor

Re: Waterfall variation by sales type

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:

 

 image.png

 

 

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.

Super User III
Super User III

Re: Waterfall variation by sales type

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.

 

Previous_Period.png

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





goyasor
Frequent Visitor

Re: Waterfall variation by sales type

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

 

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

cap.jpg

Super User III
Super User III

Re: Waterfall variation by sales type

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


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





goyasor
Frequent Visitor

Re: Waterfall variation by sales type

Here's the raw data I used in Excel:

 

ManufactuterCategory2015 Sales2016 SalesVariance 2015-16
GMCountry A  1,000,0002,000,0001,000,000
GMCountry B1,000,0002,400,0001,400,000
FordCountry A3,000,0001,900,000-1,100,000
FordCountry B4,000,0003,000,000-1,000,000
ToyotaCountry A1,000,0002,000,0001,000,000
ToyotaCountry B3,000,0003,800,000800,000
HondaCountry A2,000,0001,000,000-1,000,000
HondaCountry B2,500,0002,000,000-500,000
ChryslerCountry A500,000900,000400,000
ChryslerCountry B1,000,0002,000,0001,000,000
TOTAL 19,000,00021,000,000 

 

 

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

2015 Sales19,000,000
GM2,400,000
Ford-2,100,000
Toyota1,800,000
Honda-1,500,000
Chrysler1,400,000
2016 Sales21,000,000

 

 

Then finally the Waterfall chart:

image.png

Vvelarde Community Champion
Community Champion

Re: Waterfall variation by sales type

@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

 

WFall.png




Lima - Peru

Helpful resources

Announcements
‘Better Together’ T-Shirt Contest – Winner Announced!

‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors