Percent in pivot table

[stark___]

Hello,

Can you help me with my problem? I have a databaselike this:

Store	Supplier	Sales
(001) MYT	Trade	52 993
(001) MYT	Johns	31 731
(001) MYT	Spring	24 055
(001) MYT	Freed	36 722
(001) MYT	Sun	25 179
(002) KOM	Trade	42 291
(002) KOM	Johns	52 873
(002) KOM	Spring	22 046
(002) KOM	Freed	36 465
(002) KOM	Sun	28 927
(003) MAR	Trade	59 557
(003) MAR	Johns	65 533
(003) MAR	Spring	24 670
(003) MAR	Freed	42 320
(003) MAR	Sun	38 030

I want to get a pivot table, that will show me a percent of supplier's sales in every stores when I choose defined supplier.

I can create pivot table like this: (I use sum(Sales)/sum(total<Store>(Sales))

Store	Supplier	%
(001) MYT	Freed	21,52%
	Johns	18,59%
	Spring	14,09%
	Sun	14,75%
	Trade	31,05%
(001) MYT Total		100,00%
(002) KOM	Freed	19,97%
	Johns	28,96%
	Spring	12,07%
	Sun	15,84%
	Trade	23,16%
(002) KOM Total		100,00%
(003) MAR	Freed	18,39%
	Johns	28,48%
	Spring	10,72%
	Sun	16,53%
	Trade	25,88%
(003) MAR Total		100,00%

but I want to get table like this: (for example: I chose Trade)

Store	Supplier	%
(001) MYT	Trade	31%
(002) KOM	Trade	23%
(003) MAR	Trade	26%

Thanks for you help, Stark

[MayilVahanan · Accepted solution]

HI

Try like this

sum(Sales)/sum({1}total<Store>(Sales))

Please check the attached file..

[stark___]

Thank you, it works perfect. But I have more difficult case now: providers supply goods to stores in different categories:

Store	Supplier	Category	Sales	Date
(001) MYT	Freed	Soap	8588,33	01.06.2012
(001) MYT	Freed	Shampoo	3263,6	01.06.2012
(001) MYT	Freed	Cream	2959,36	01.06.2012
(001) MYT	Trade	Soap	6837,41	01.06.2012
(001) MYT	Trade	Shampoo	10777,3	01.06.2012

and if I want to see a percent of supplier's sales of all categories in every stores, sum(Sales)/sum({1}total<Store>(Sales)) works perfect, but if I want to see a percent of supplier's sales of certain category, it shows me wrong  value. See attached file.