Dividing two Aggr() Functions

[chhavi376]

District Code	Product1	Value
District 1	Prod1	1
	Prod2	2
	Prod3	3
District 2	Prod1	4
	Prod2	5
	Prod3	6
	Prod4	7

I want to calculate the district wise share for every product.

the expression i am using is:

num(aggr(Sum(Value), Products,District)/aggr(Sum(Value), District) ,'#,##0.0%' )

But, this expression is not working.

Could anyone please suggest a way

[rubenmarin · Accepted solution]

Hi chhavi, I see two options:

- Use NODISTINCT:

num(aggr(Sum(Value), Product1,[District Code])/aggr(NODISTINCT Sum(Value), [District Code]) ,'#,##0.0%' )

- Do the percentage in only one aggr:

num(aggr(Sum(Value)/ Sum(TOTAL <[District Code]> Value), Product1,[District Code]) ,'#,##0.0%' )

[effinty2112]

Hi Chhavi,

i don't think you need AGGR here at all:

District Code	Product1	sum(Value)	sum(Value)/sum(TOTAL <[District Code]> Value)
District 1	Prod1	1	16.67%
	Prod2	2	33.33%
	Prod3	3	50.00%
District 2	Prod1	4	18.18%
	Prod2	5	22.73%
	Prod3	6	27.27%
	Prod4	7	31.82%

cheers

Andrew

[chhavi376]

Thanks Ruben.

It's working fine

[chhavi376]

Andrew,

I was trying to calculate the district wise share for every product in the dimension(This will remain static for me).

Also, I had to create a Pivot table, and i did not want the contents of this static dimension to move with the scroll bar, therefore i could not use it as an expression.