Waterfall Chart of Product Pricing

[atifsiddiqui]

Need help in building a waterfall chart. Here is my data:

No.	Product	Supplier Cost	Shipping Cost	Inventory Cost	Profit Margin	Retail Cost
1	Sample Product 1	12	5	3	8	28
2	Sample Product 2	45	32	12	20	109
3	Sample Product 3	10	2	3	6	21
4	Sample Product 4	15	7	4	12	38
5	Sample Product 5	17	9	4	12	42
6	Sample Product 6	25	8	5	15	53
7	Sample Product 7	32	12	15	20	79
8	Sample Product 8	38	25	22	17	102

Each row is one product and all the columns are individual costs, the last column is the sum of all. I need a waterfall chart for each product. I have a filter slicer of the product names. When I select one, this waterfall chart should show the first pillar of Retail Cost and then the waterfall of breakdown of Supplier Cost, Shipping Cost, Inventory Cost, Profit Margin. 

When nothing is selected on the slicer, then it would show the full total of Retail Cost as first pillar, then break down of other columns in waterfall.

How do I do that? 

[alish_b · Accepted solution]

Hey @atifsiddiqui ,

The cleanest way to attain this will be by unpivotting the data in Power Query. Your data will look something like this:

Now, I added a Sort Order column so that I can get full control in the waterfall category sort order (You will have to click on Cost Type column in Table View, Under column tools>Sort by column>SortOrder). You could skip this as well if the order of columns other than Retail Cost is not important.
Now you will need to create one measure as follows:

Waterfall Value =

VAR CostType = SELECTEDVALUE(ProductData_Unpivoted[Cost Type])

RETURN

IF(

    CostType = "Retail Cost",

    SUM(ProductData_Unpivoted[Amount]),

    -SUM(ProductData_Unpivoted[Amount])

)
What we are doing above is, Retail Cost will be returned as a positive value, and remaining as negative values --> Retail Cost will be then shown as Increase and remaining as Decrease, making the required waterfall structure.
Now place the, Cost Type in the Category and the above measure (Waterfall value) in the Y-axis. You will get something like this:

Now, remove the legend
Under Columns>Turn off Show total column & change the decrease value to a color of your choice
Turn on data labels. You will have something like this:

Now, obviously I don't want those negative values there, so click on the measure Waterfall Value and under Measure tools in the ribbon, Change format from General to Dynamic and paste this: 

"#,##0;#,##0" 
This formats both positive and negative values as positive (just display is changed but value is preserved)
You should have this:

Hope it helps!

PS: Even when you are unpivoting, the connection to source is still maintained so the data should update when there are changes in the source during refresh. It is always best to test anyway.

[Kedar_Pande]

@atifsiddiqui 

You need to unpivot your data in Power Query.

Select all cost columns except Retail Cost, then Transform > Unpivot Columns. This creates "Attribute" (cost type) and "Value" columns.

If this answer helped, please click Kudos or mark as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

[atifsiddiqui]

That's great. However, I have several other columns in the same table that are now getting messed up. Is there a way I can make a new table separating these 4 columns from the main table and do this unpivot on the new table. While keeping the reference alive, so that whenever any changes happen in the excel sheet, I come to Power BI and refresh and it picks those changes and also updates this new table with these 4 columns that are now unpivoted?