Share of Total including not shown items

[#1]

Hello Community,

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?

Would appreciate your ideas very much

Thanks

Alejandro

[kaushiknsolanki]

Hi,

Have a look at the application attached.

Regards,

Kaushik Solanki

[#3]

Thanks Kaushik,

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?

Regards

Alejandro

[marcelo_7]

Total sales:

sum(aggr(sum({1}<Week>TOTAL Sales),Week,[Article in Promo]))

The Total will change if you make selections so for every selection you want to be reflected in the total you add the FIELD=p(FIELD) as in:

sum(aggr(sum({1}<Week>TOTAL Sales),Week,[Article in Promo])) Hope it helps!