Sum grouped by

[Peve]

Hi all,

I have the following problem: in a table I need to calculate the ammount sold by each consultant in a specific team divided by the total ammount sold by that person (independently from where he/she has sold).  

Region	Team	Consultant	Units sold	Calculation required
Region 1	Team 1	Marco	3	=3/(3+6) = 33.3%
Region 1	Team 2	Marco	6	=6/(3+6) = 66.6%
Region 2	Team 3	Gianni	4	=4/(4+0) = 100%
Region 3	Team 4	Gianni	0	= 0/(4+0) = 0%
Region 1	Team 2	Franco	2	= 2/2 = 100%

I have already tried the following formula: sum(aggr(count(products),Consultant)), but I then get this as a result:

Region	Team	Consultant	Units sold	Calculation required
Region 1	Team 1	Marco	3	=3/(3+6) = 33.3%
Region 1	Team 2	Marco	6	=0%
Region 2	Team 3	Gianni	4	=4/(4+0) = 100%
Region 3	Team 4	Gianni	0	= 0%
Region 1	Team 2	Franco	2	= 2/2 = 100%

which is unfortunately wrong. 

Any help will be highly appreciated. 

PS: I can only operate in a Set Analysis or with variables. The loadscript for this app is way to complicated for my basic understanding. 

[vinieme12]

As below, corrected

Sum(Unitssold)/Sum(TOTAL <Region,Consultant> Unitssold)

[Andrea_Spinetti]

Hi @Peve, you may try this: Sum([Units sold]) / Sum(TOTAL <Region,Consultant> [Units sold])

[Andrea_Spinetti]

Ops, I've just seen @vinieme12 already provided the solution 😆

[Peve]

Hi,

thank you for your reply, I did try that one, but however, for some reasons it was aggregating only on the Region and not on the Consultant. 

[Peve]

Hi,

thank you for your reply, however I have already tried that one. Unfortunately it was aggregating only on the Region and not on the Consultant. Has to be said, that in the "count" I have quite a list of expressions.