Aggregated and non aggregated in a calculated field

Question & Answer
1

Hi all!
we are having doubts in a calculated field as we want to do an aggregated level but comparing against the value of a (non-aggregated) column.
The simplified calculation would be like this:
ifelse({FIELD}= ‘A’,
sum({CALC_NUM}),
avg({CALC_NUM})
)
Here ‘FIELD’ is a text field and we want that depending on its value it sums or averages for a measure.
We get the following error:
A grouping function is missing. Custom aggregations cannot contain both aggregation “SUM” and non-aggregation “SUM(“CALC_NUM”)” fields in any combination.

What are we missing? I would appreciate any help to enlighten us.
Many thanks

2

Can you try wrapping the if statement in an aggregation?

ifelse(firstValue({FIELD},[{FIELD} ASC],[{FIELD}}])=‘A’,sum({CALC_NUM},avg({CALC_NUM}))

Let me know if that works

1 Like
3

Yes! it’ works, many thanks!