aggr function in pivot table

[wanyunyang]

My data have three columns:

Salesrep, Customer, Sales

RepA, CustomerA, 2

RepA, CustomerB, 4

RepB...

...

I would like to create a report by dimension: Salesrep & Customer. In one of the measures, I need to show avg sales by rep on the customer level as well, such as:

Salesrep, Customer, AvgSalesByRep

RepA, CustomerA, 3

RepA, CustomerB, 3

RepB...

...

What aggr function should I write here to implement this?

Thank you in advance!

[chrismarlow]

Hi,

Try;

avg(TOTAL Sales)

Cheers,

Chris.

[wanyunyang]

Hi there,

Sorry, I should have clarified this in the question. Rep A is not the only sales rep, so avg(Total Sales) wouldn't work.

[chrismarlow]

Hi,

Ahh, try;

avg(TOTAL <Salesrep> Sales)

Cheers,

Chris.

[wanyunyang]

No luck 😞 Each rep ends up with the same number

[chrismarlow · Accepted solution]

Hi,

Not sure I follow then;

Cheers,

Chris.

[wanyunyang]

Yes you're right! I tried again and it worked. Thank you!