Aggregation in pivot table

[patrickanderson]

Hello,

I am pretty sure this can be done and I am fairly sure I have done it before but I am having a brain cramp and jsut need some quick help:

I have a Pivot Table:

TDLINX	Category	Dollar Sales	Dollar Sales % Chg vs. YAGO:	$ Share of Category
84	Enlmgr	49420	16.1%	100.0%
	Cxpnxg	11918	5.6%	-
	Mveaqlp	10534	-19.1%	-
	ApnhtOxgop	5582	-7.3%	-
101	Enlmgr	56131	10.9%	100.0%
	Cxpnxg	12542	6.4%	-
	Mveaqlp	10391	-18.7%	-
	ApnhtOxgop	7570	-9.5%	-

'Enlmgr' is in the Field Category but it is more of a total of the other 3 Catergories plus a few others who arent reported.

I need to calculate the % of 'Enlmgr' is for each Category and TDLINX.

I tried

=

(sum({<$(vMaxMonth)>}[Dollar Sales])/aggr(sum({<$(vMaxMonth), Category={'Enlmgr'}>} [Dollar Sales]),TDLINX, Category)

but that didnt work I also tried the aggr around the whole expression.

Any Ideas?

[patrickanderson]

Do I need to add a different level added in the script?

[erichshiino]

I didn't understand your question...

First,

You got the results only for the line with Enlmgr because you put that on the set analysis to divide the number (the calculation is only possible for this line.

do you need the % related to the total of category?

You can use total. Try:

sum({<$(vMaxMonth)>}[Dollar Sales])/ sum({<$(vMaxMonth)>}total [Dollar Sales])

or

Hope it helps,

sum({<$(vMaxMonth)>}[Dollar Sales])/ sum({<$(vMaxMonth)>}total <Category> [Dollar Sales])

or

sum({<$(vMaxMonth)>}[Dollar Sales])/ sum({<$(vMaxMonth)>}total <TDLINX> [Dollar Sales])

[patrickanderson]

Imagine the data came in like this:

Category	$$
Nike	20
Adidas	15
Reebok	10
Shoes	55

The Category Shoes is reported as a Category but it actually contains the sum of Nike, Reebok and Adidas PLUS a few other smaller shoe brand whose individual numbers are not reported. I want to calculate market share for Nike Reebok and Adidas.

Like Nike/Shoes= 20/55 = 36%

The issue is all the data comes in on the same level "Category". I could move the Shoes amount to a differnt field but I want all my other calcs to work on the Category level. Is this possible?

[erichshiino · Accepted solution]

I think I understood it now.

If you don't mind hardcoding this category as you showed on the first post, you can include Shoes on set analysis with Total.

Thus, you can try:

sum({<$(vMaxMonth)>}[Dollar Sales])/ sum({<$(vMaxMonth), Category={Shoes}>}total <TDLINX> [Dollar Sales])

sum({<$(vMaxMonth)>}[Dollar Sales])/ sum({<$(vMaxMonth), Category={Shoes}>}total <Category> [Dollar Sales])

or

sum({<$(vMaxMonth)>}[Dollar Sales])/ sum({<$(vMaxMonth),Category={Shoes}>}total [Dollar Sales])

or

[patrickanderson]

But would this work with multiple TDLINX? the total function woudl take the the Application total correct so if

TDLINX	Category	$$
1	Nike	20
1	Adidas	15
1	Reebok	10
1	Shoes	55
2	Nike	25
2	Adidas	30
2	Reebok	20
2	Shoes	100

total $$ woudl be 155 so now NIKE Market share for TDLINX 1 would calculate as 20/155 = 12.9%

Is there any way to avoid this?

Thanks for your help by the way!!

[erichshiino]

That will be the result for the 3rd expression, the 2nd must give the result that you want (may be the 1st )