Chris Dunhill 3 Posted April 4, 2017 Share Posted April 4, 2017 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 Link to comment Share on other sites More sharing options...
Sean Riley Posted April 10, 2017 Share Posted April 10, 2017 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 Link to comment Share on other sites More sharing options...
Khushboo Rabadia Posted November 21, 2017 Share Posted November 21, 2017 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. Link to comment Share on other sites More sharing options...
Sayali Patil Posted February 12, 2019 Share Posted February 12, 2019 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. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now