Contribution to Growth Data

Question & Answer
,
1

I have the below required columns, (x) means negative.

Product - Delta - % Delta - % of Total Delta - CtG
A (10.51) (66%) (4%) (0.8%)
B (5.5) (5%) (3%) (0.4%)
C (4.84) (22%) (2%) (0.5%)

TOTAL 281.5 20.5% 100% 20.5%

The first 3 I can get. I’m not sure how to get the data to be shown on the 4th column like that. Basically, all values of that column should add up to the total of the 2nd column (20.5%). At the moment I have 2 calculated fields for the 3 columns. Delta which is equal CV - PV. Delta %= sum(CV)/sum(LV) - 1. The 3rd column uses Delta field but as a table calculation (that’s why I use CV - PV for this field instead of sum(CV) - sum(PV). The formula to get the 4th column is Delta/Total Delta*Total %Delta. So we can say for product A, the sales decrease by 10.51 which is minus 0.8 percent (or -80 basic points) of the 20.5% growth of all products.

Thanks in advance.

2

Can you take the sumOver(% Delta) and multiply it by the % Of Total Delta?

sumOver(% Delta) should always be 20.5% in this case and then you can just multiply by the percentOfTotal change.

Let me know if that works or if I’m looking at it incorrectly.

3

Hey Max,

Are you saying for example product A, the number should be 4%*20.5%? Then yes, it’s a right calculation. However, I’m not sure how to write the sumOver(% Delta) correctly and here’s why. Let’s say I have this hierarchy for sales: region > country > state. And I already create Delta charts for each of them (1 for region, 1 for country and 1 for state). Now let’s say that 20.5% is for all regions. At the moment, I have 1 delta chart for each hierarchy and “actions” so that when a region is clicked, rest of the charts will reflect data for that region only. And the same idea will apply for this products data, meaning 20.5% will not be correct anymore once I select a region/country/state

So I think the ultimate question is what the formula for this calculated field should be so that when I show it on the table, it will be exactly like what I want (0.8% for product A, 0.4% for B, etc. and 20.5% for the total). Note that I do not want to show the region, country, and state. I just want to show the product name like that.

Another note is my hierarchy is just an example. it’s much more complicated than that and that’s why I didn’t try going with the sumOver route yet

Long

4

sumOver it will take into account all of the filters applied to it, if you don’t add PRE_FILTER or PRE_AGG to it.

So if you are filtered to just one region it will sum only just over that region.

You can also look into partitioning it by region, country, state to get sums per those based on the groups if that’s what you are looking for.

5

Thanks Max. I tried sumOver({% Delta},,POST_AGG_FILTER) and it doesn’t work. Also, what should be the logic for “% Of Total Delta”. Right now, it’s a table calculation I apply right on the visual for the field “Delta”

Update: Nevermind Max, I found a way. Instead of sumOver({% Delta},,POST_AGG_FILTER), I did the sumOver for each of the field that contributes to the %Delta field. Thanks a lot for your guidance!

1 Like