Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
v-yulgu-msft
Employee
Employee

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.

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.

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 Super User!

Check out my blog: Power BI em Português



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

 

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

cap.jpg

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 Super User!

Check out my blog: Power BI em Português



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

@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

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] ) )
)

 

 

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.