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.

Khushboo R - Nov 21, 2017 - 10:00am ::

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?

Sean Riley - Apr 10, 2017 - 7:51am ::
+ Add a Comment

(1) Answer

Login