Waterfall Chart with PowerPivot

[AlexNYExcel]

Hello Everybody,

probably every expert pro know how to create waterfall chart in excel.
Just in case here is explanation:
1. For example we have list of selling categories that contribute to overall sales in period
2. we need view, where 1st column - categories, 2nd column - sales by categories, in the end we Sum them up
3. We create 2nd column with "empty values" (that will be bottom in the chart) - where for 1st category value 0, for next one is equal to sum of all values before.
For example
Shoes 5
Jeans 10
Jackets 7
Sum 22

With "empty values" (will allow next category start not from 0)

Shoes 5 0
Jeans 10 5
Jackets 7 15
Sum 22 (sum from 0 again, to show height of the total)
4. We create chart, and the bottom categories "empty values" make 100% transparent.
Result something like this:
http://3.bp.blogspot.com/-i8pk8dQEZxY/UJ6EkykbaHI/AAAAAAAAAgM/dYeWXtEDWfE/s1600/waterfall9.png

So my question is, how to create such measure in powerpivot, after creation pivot with categories and sales?

 

[Ozeroth]

Hi,

I had a play with this, and this is the approach I took:

1. Choose a dimension to use as 'categories' of the waterfall (e.g. Product Category).
2. Enforce an ordering on that dimension using an index column.
3. Create an extended version of the above dimension table by adding a 'subtotal' item after each element of the dimension, also with an index column to preserve ordering. This allows you to optionally have a 'total bar' after any 'increment bar'. This extended table will actually be used for the chart.
4. The extended dimension table relates to the original dimension table - see uploaded file.
5. Create measures for:
   Cumulative total
   Previous Cumulative total
   Padding bars
   Increment bars (positive/negative and above/below axis)
6. Put these all in a pivot table/chart with appropriate formatting

This did get reasonably complicated, so I have uploaded an example done with Excel 2010 Power Query & Power Pivot:
https://drive.google.com/open?id=0B9pNjpDQKy_LVS1uZGo4Mlp2Qk0

The 'Category' slicer lets you choose which products are available, and the ChartCategory slicer lets you choose product/subtotal bars.
The nice thing is that, if you exclude a product category, it is excluded from the waterfall calculation.
I'm sure this can be improved on, but at least demonstrates that it can be done

 

[AlexNYExcel]

Thank you for the solution.
You did a great job, actually most of the value added me "Cumulative Measure".
I simple used that and 2nd one, for "empty" column - subtracted Actual from Cumulative and it works fine.

And for total another measure as well.

Hi,

I had a play with this, and this is the approach I took:

1. Choose a dimension to use as 'categories' of the waterfall (e.g. Product Category).
2. Enforce an ordering on that dimension using an index column.
3. Create an extended version of the above dimension table by adding a 'subtotal' item after each element of the dimension, also with an index column to preserve ordering. This allows you to optionally have a 'total bar' after any 'increment bar'. This extended table will actually be used for the chart.
4. The extended dimension table relates to the original dimension table - see uploaded file.
5. Create measures for:
   Cumulative total
   Previous Cumulative total
   Padding bars
   Increment bars (positive/negative and above/below axis)
6. Put these all in a pivot table/chart with appropriate formatting

This did get reasonably complicated, so I have uploaded an example done with Excel 2010 Power Query & Power Pivot:
https://drive.google.com/open?id=0B9pNjpDQKy_LVS1uZGo4Mlp2Qk0

The 'Category' slicer lets you choose which products are available, and the ChartCategory slicer lets you choose product/subtotal bars.
The nice thing is that, if you exclude a product category, it is excluded from the waterfall calculation.
I'm sure this can be improved on, but at least demonstrates that it can be done

 

[ImkeF]

Here's a fully Power Query version of it: No subtotals, but and end column as well
Waterfall Chart using Power Query | The BIccountant
Imke