Aggr question - trying to calculate market totals

[#1]

Hi,

I'm trying to calculate a market share for some products

Market	Product	Sold	Market Total	Market Share %
M1	P1	100	200	50
M1	P2	50	200	25
M1	P3	50	200	25
M2	P4	20	70	28,57142857
M2	P5	50	70	71,42857143

Does anybody know if this is possible using the aggr() function? If so, how do I do this?

The "Sold" column is an easy expression like =Sum([Value]). the "Market Total" column should contain the total value of the entire market. I tried the =Sum(TOTAL [Value]) function, but this one returns off course 270 for every row. Next I tried =Aggr(Sum([Value]),[Market]) but that doesn't seem to do the trick

Any ideas?

[nilesh_gangurde]

Try this.

Aggr(Sum([Value]),Market,Product)

and for total

Aggr(Sum( Total [Value]),Market,Product)

-Nilesh

[jerem1234 · Accepted solution]

Try:

sum(total <Market> Value)

Hope this helps!

[alexandros17]

Your market share is

(Sold)/sum(MarketTotal)

Hope it helps

[#5]

that gives me this result:

Market	Product	Sold	Market Total	Market Share %
M1	P1	100	100	50
M1	P2	50	50	25
M1	P3	50	50	25
M2	P4	20	20	28,57142857
M2	P5	50	50	71,42857143

I understand why, because this tries to aggregate the [Value] field per Market and Product dimension, which is basically the same as using the expression =Sum([Value]).

to add some more info: i'm only showing the Market and Product dimension in this table, but on my dashboard, there are several other dimension available for filtering purposes

[jvishnuram]

Hi Bart,

Aggr is just like a group by clause, it will give you the summing total based on Market only like adding all M1 and gives as 200 and adding all M2 and gives as 70 for all the relevant rows.

[#7]

thanks for the quick response! Never knew the expression could be so simple

[maxgro]

sum(Value) / sum(TOTAL <Market> Value)