Expression for comparing avg values

[#1]

Hi,

I want to write an expression that checks if a value is higher than the average values of the same sort.

Say there's 10 sales persons all of which have an individual profit per month. I can see the average profit per sales person by writing avg(salesprofit). That works fine. But what I want to do now is to write an extra expression that checks if that average number is higher than the average of all average profit per sales persons and then connect that value with the text color of the value.

Example of Avg(salesprofit)

John: 5

Lisa: 7

Nick: 9

since the average of the sales persons combined are 7, Johns number should be red (below average) and Nicks should be green (above average). Right now I'll write that as: if(Avg(ProfitPerProduct)>7,RGB(152,234,117),RGB(241,89,67)) but I want to change "7" to a dynamic expression that calculates the average instead of manually hard coding it there.

[spividori]

Hi.

Try the following:

if(Avg(ProfitPerProduct)>Avg(all ProfitPerProduct),RGB(152,234,117),RGB(241,89,67))

[#3 · Accepted solution]

Hello Martin,

you can use this:

if( Avg(ProfitPerProduct) > Avg( TOTAL ProfitPerProduct) , RGB(152,234,117),RGB(241,89,67))

Regards, Roland

[#4]

Thank you Roland! Works great.

As a follow up question, how do I make the header of a pivot table not getting affected by my color changing expression? Right now the header is the same color as the first value (if the first value is green, the header is also green etc).

I found that there is a special tab for that matter with pivot tables.

Anyhow, your suggestion with TOTAL only seems to work with avg. Is there a way to make it work with sum and count as well?

[#5]

Hello Martin,

Glad to help You. TOTAL is also availible within sum() or count(). What I need from time to time is like "

sum( Value) / sum( total Value)". Or also in conclusion with SET very useful; see forum for more details (examples).

RR

[#6]

What I ment with that it doesn't work with SUM is that in reality the numbers will be wrong. Example with number of sales in two different months and totality of both months:

John: 2 + 3, (sum 5) (avg 2,5)

Lisa: 4 + 6, (sum 10) (avg 5)

Nick: 8 + 7, (sum 15) (avg 7,5)

The average of the total is 5, which works fine as a middle line to separate the lower values as colored red and the higher values as colored green.

But if I apply the same strategy to Sum, using "if larger than total sum" noone will ever be able to have more sales than the total sum, since that would be 30. So I need to include Avg somewhere, like "the average value of the total sum".

In this case the colour green would be on the values which has a higher sum than the average sum 10 (Nick 15).

I've tried different variations of AVG(TOTAL SUM(Sales)) and TOTAL AVG (SUM(Sales)) etc but I can't seem to get it right...

[#7]

Ah! I found the answer.

I need to use AGGR. So the result is: avg( aggr( sum(ProfitPerOrder), EmployeeName))

[#8]

Hi Again,

sorry, I was too late, but therefor you had your feeling of success!

That's it. If you implement a nested aggregation you have to use the aggr() "between" the two functions.

avg( TOTAL aggr(sum(Qty), EAN))

RR