how to get the total values of dimension attribute ?

[#1]

Hello,

For instance, I have Four attributes( Country,Category,Product and Sales) and i wanna find the the contribution of each category by country.

I put the Category and Country as dimension then created the expression to find the Contribution(=Num(Sum(Sales)/Sum(TOTAL Sales),'0%')

Sum(TOTAL Sales),'0%')  - this calculate the full total of the row but i wanna calculate TOTAL by country. For my expression i getting like below pivot table:

		FR	GER	UK	NL	SPA	IT
Automotive & Gadgets		1%	0%	0%	0%	1%	0%
Fashion		3%	3%	3%	0%	6%	1%
Home & Living		4%	6%	11%	1%	5%	3%
Home Appliances		4%	9%	9%	1%	8%	1%
Lifestyle		0%	-	-	0%	-	-
Media, Games & Music		0%	0%	0%	0%	0%	0%
Sports & Outdoors		0%	2%	1%	0%	2%	1%
Toys, Kids & Babies		2%	3%	2%	0%	2%	1%
Travel & Luggage		1%	1%	0%	0%	1%	0%
Total		15%	25%	27%	3%	24%	7%

but I want the pivot table like shown below:

		FR	GER	UK	NL	SPA	IT
Automotive & Gadgets		20%	0%	0%	0%	1%	0%
Fashion		23%	3%	3%	20%	6%	1%
Home & Living		4%	6%	11%	1%	5%	3%
Home Appliances		40%	9%	9%	4%	8%	1%
Lifestyle		0%	60%	70%	0%	56%	80%
Media, Games & Music		0%	0%	0%	0%	20%	3%
Sports & Outdoors		0%	2%	1%	45%	2%	1%
Toys, Kids & Babies		4%	5%	2%	30%	2%	1%
Travel & Luggage		8%	15%	3%	0%	1%	10%
Total		100%	100%	99%	100%	100%	100%

Could you please advise me how to fix this one?

Best

Robert

[Gysbert_Wassenaar]

Try Sum(Sales)/Sum(TOTAL <Country> Sales)

[#3 · Accepted solution]

Hi,

Many thanks for your reply. you logic exactly correct.

=Num(Sum(Sales)/

if(COUNTRY='FR',Sum(TOTAL{$<COUNTRY={'FR'}>}Sales),

if(COUNTRY='GER',Sum(TOTAL{$<COUNTRY={'GER'}>}Sales),

if(COUNTRY='UK',Sum(TOTAL{$<COUNTRY={'UK'}>}Sales),

if(COUNTRY='NL',Sum(TOTAL{$<COUNTRY={'NL'}>}Sales),

if(COUNTRY='SPA',Sum(TOTAL{$<COUNTRY={'SPA'}>}Sales),

Sum(TOTAL{$<COUNTRY={'IT'}>}Sales)

))))),'0%')

above is the answer for my question.

Best

Robert