Percentages in Waterfall Chart

[Chris Dunhill 3]

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

[Sean Riley]

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

[Khushboo Rabadia]

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" end2) 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.

[Sayali Patil]

Hello,

Workaround (as shared earlier as a comment)-

 

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.