waterfall

[Cour002]

Hello,

I have a dataset with a row for each month and various plants, let's just consider plantA and plantB.  There is an initial cost column CostA.  There are also columns for added costs, CostB, CostC, CostD, and a totalCost = [CostA + CostB + CostC + CostD]. What I would like to do is be able to do is get a waterfall chart that has my CostA on the left, then CostB, CostC, CostD as the 'waterfall bars' and totalCost as the far right bar.  Then I would like to be able to be colored by legend plant (plantA and plantB).  Is this possible?  If so, how can I make it happen?  

Thanks!

[v-juanli-msft · Accepted solution]

Hi @Cour002

I assume your dataset structures as below

To create a waterfall chart, I create a new table based on the original table by the following formula:

Table =
VAR TABLE1 =
    SUMMARIZE ( Sheet1, [plants], [CostA], "cost category", "Cost A" )
VAR TABLE2 =
    SUMMARIZE ( Sheet1, [plants], [CostB], "cost category", "Cost B" )
VAR TABLE3 =
    SUMMARIZE ( Sheet1, [plants], Sheet1[CostC], "cost category", "Cost C" )
VAR TABLE4 =
    SUMMARIZE ( Sheet1, [plants], [CostD], "cost category", "Cost D" )
VAR TABLE5 =
    SUMMARIZE ( Sheet1, [plants], [ totalCost], "cost category", "totalCost" )
RETURN
    UNION ( TABLE1, TABLE2, TABLE3, TABLE4, TABLE5 )

Then in the waterfall chart, add column “cost category” and “Cost” to “Category” and “Y Axis” Fields.

“a waterfall chart that has my CostA on the left, then CostB, CostC, CostD as the 'waterfall bars' and totalCost as the far right bar”

To achieve this, you could select three dots on the upper right of the visual to sort by category

However, what are difference from your requirement are as follows:

The “Total” bar on the right occurs as the chart created since it counts the total for all categories.

The chart can’t be color by legend, It is colored by category “Increase, Decrease, Total”.

Best Regards

Maggie

[Cour002]

This worked great Maggie, thanks so much! 

Is there any way to take this one step further and have each bar colored by a category (in this example each plant getting its own portion of the total in each category a certain color)?