Return Different aggregations Based on a dimension value

Question & Answer
, , , , ,
1

I need to return Sum and Avg of a measure based on different values of a dimension.

For example I have Dimension ‘Unit’ with values Percentage, Dollar, Days, Count.
And Measure as ‘KPI’
My requirement is If Unit = Days or Count I need to return Sum of KPI else I need to return Avg of KPI
How can I do this calculation. While trying to do I am getting aggregation mismatch error.

2

Hi @Ganga ,

to solve the aggregation mismatch error, you can create two separate ifelse statements.
One of them will always be 0. By surrounding the ifelse statements with your aggregation you avoid the aggregation mismatch error.

sum(
    ifelse(
        {Unit}= "Days" OR {Unit}= "Count" , // IF Unit is Days or Count
        {KPI},
        0
    )
)
+
avg(
    ifelse(
        {Unit} <> "Days" AND {Unit} <> "Count", // IF Unit is not Days or Count
        {KPI},
        0
    )
)

Best regards,
Nico

1 Like