Percentages in Waterfall Chart

Hello,

Is it possible to get use the Waterfall Chart to display variances in calculated percentages? The format of the data I am looking at is shown below.

Department | Type | Product | Category | Value £ |

Produce |
Fruit | Apples | Profit LY | 10 |

Produce | Fruit | Apples | Profit Variance | 2 |

Produce | Fruit | Apples | Sales Last Year | 18 |

Produce | Fruit | Apples | Sales Variance | 3 |

Produce | Fruit | Pears | Profit Last Year | 40 |

Produce | Fruit | Pears | Profit Variance | -5 |

Produce | Fruit | Pears | Sales Last Year | 75 |

Produce | Fruit | Pears | Sales Variance | -10 |

Produce | Veg | Beans | Profit Last Year | 15 |

Produce | Veg | Beans | Profit Variance | 3 |

Produce | Veg | Beans | Sales Last Year | 25 |

Produce | Veg | Beans | Sales Variance | -4 |

Produce | Veg | Peas | Profit Last Year | 3 |

Produce | Veg | Peas | Profit Variance | -1 |

Produce | Veg | Peas | Sales Last Year | 6 |

Produce | Veg | Peas | Sales Variance | -3 |

With the above, I can easily create Waterfall charts which show the Year on Year variance for Profit (£) and Sales (£) individually, however I want to show the **%** Profit ie. Profit / Sales % in a Waterfall. And - for it to be of any use - it needs to work at all levels of aggregation.

For example:

Is this possible?

## 2 Comments

You can have following steps to achieve this:

1) Create a calculated column as - case when Find("Var",[Category])<>0 then "Var" else "Last Yr" end

2) Create waterfall chart with Type in trellis column, [Cat2] in category axis and value expression as below

(Sum(case when Find("Profit",[Category])<>0 then [Value £] else 0 end) OVER (AllPrevious([Axis.X])) / Sum(case when Find("Sales",[Category])<>0 then [Value £] else 0 end) OVER (AllPrevious([Axis.X]))) - SN(Sum(case when Find("Profit",[Category])<>0 then [Value £] else 0 end) OVER (Previous([Axis.X])) / Sum(case when Find("Sales",[Category])<>0 then [Value £] else 0 end) OVER (Previous([Axis.X])),0)

- AllPrevious will sum up values in Var and Last Yr category to give Current Yr value

- To calculate Variance you need to do CY-LY i.e AllPrevious - Previous

- You need to include SN(,0) when using Previous as Last Yr do not have any previous value and it will make that value as null if SN is not used resulting in null value for Last Yr.

Can you post a sample dxp on exactly how you calculte your Year on Year variance for Profit (£) and Sales (£) individually so that I can know exactly how you want to combine those?