Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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%' )
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%' )
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
Thanks Ruben.
It's working fine
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.