I have the following issue: I want to calculate the share of Sales of Articles included in a promotion as a % of the total sales of ALL articles. As an example I have this:
The raw data is like this:
Article
Week
Sales
Promo (y/n)
a
1
10
y
b
1
10
y
c
1
10
n
a
2
20
y
b
2
10
y
c
2
20
n
I am trying to buid a pivot like this:
Only show the elements of Article where Promo = 'y'
Week
Article in Promo
Sales
Total Sales
% of Total Sales
1
a
10
30
33%
b
10
30
33%
2
a
20
50
40%
b
10
50
20%
For the calculation of the Total Sales I have been trying the following
1. When I use:
sum( { < Promo = > } TOTAL Sales)
I get 80 in each row, which is the grand Total of all Articles, but I want it to be by week.
2. When I use:
sum( { < Promo = > } TOTAL < Week > Sales)
I get 20 for Week 1 (instead of 30) and 30 for Week 2 (instead of 50)
Now I get the sum by week but not for all articles, but only for the ones shown in the table.
Is there a way of getting the total sum for all articles by week, as in the table above?
the thing is that it works as long as the article "c" is shown in the rows. Is there a way to show only a and b and still have the Total Value from a, b and c in the Total Sales column?
