4 Replies Latest reply on Oct 4, 2017 7:35 AM by robin.o

# Sum and average in one table

Hi all,

so i'm trying to build a dashboard where i can show the average and sum of values in one table based on the value in a column called Unit.

If it's an %, it should show the average of the values. If it's # it should show the sum of the values.

I can't seem to get it to work and now i'm thinking it's not possible, but maybe one of you knows it. I'm using 10.3

So this should be my goal:

Code
UnitValue
AA-001%23,8
AA-002%23,1
AA-003#20
AA-004%10,8
AB-001#10
AB-002#12

I tried it with a formula, but it still shows the sum:

IF [UNIT] = "#" THEN

[REALISATION]

ELSE

[AANTAL]/[REALISATION]

END

• ###### 1. Re: Sum and average in one table

Hi Robin,

Try the formula below:

IF [UNIT] = '#' THEN

SUM([REALISATION])

ELSEIF [UNIT] = '%' THEN

AVG([REALISATION])

END

• ###### 2. Re: Sum and average in one table

Tried that, but unfortunately no success.

It says:

Cannot mix aggregate and non-aggregated comparisons or results in 'IF' expressions

• ###### 3. Re: Sum and average in one table

Wrap the [Unit] in an ATTR()

• ###### 4. Re: Sum and average in one table

Wohoooooooo TNX guys. It worked

Full formula is now

IF ATTR([UNIT]) = "#" THEN

SUM([REALISATION])

ELSE

AVG([REALISATION]/[AANTAL]) * 100

END

2 of 2 people found this helpful