Calculated measure that conditionally sums or averages

Hello,

I have a dataset that aggregates data in SQL. I need to create a measure that conditionally provides either the average of averages or sum of sums depending on the value of another field. I’ve created visuals and measures that work for the most part.

However, I am having an issue when I try to create a visual that can be filtered by another field in the dataset. The visual displays correctly if that field is included in the visual, but it provides the incorrect answer when I remove the field from the visual.

Here is the visual with the field included, which yields accurate data:

However, I want to remove the field from the visual but still filter by the field. In this scenario, I want the average of the averages, and then the sum of the sums. But everything gets summed instead:

Here is the formula for Previous Quarter:
ifelse(
in (METRIC, [‘Average ARR’, ‘Conversion to SQLs’, ‘Win Rate’]
),
avgOver({PREVIOUS_QUARTER}, [METRIC, SEGMENT, CATEGORY], PRE_FILTER),
sumOver({PREVIOUS_QUARTER}, [METRIC, SEGMENT, CATEGORY], PRE_FILTER)
)

Is it possible to provide the above output, except I get the average of the average for ‘Average ARR’, ‘Conversion to SQLs’, ‘Win Rate’, and then I still get sum of the sums for the other values?

In the current measure, I need to dicatate the aggregation type in the visual field well. I can either aggregate as Sum or Average.

Thanks!

Hi @jonfrank3366,
In regards to removing the field from your visual, you could just hide that field from your visual:

That way the visual will still include the field in your filtering. Let me know if you have any additional questions or if this works for your case.

If I hide the column it’ll still break out the values by Segment, but then Segment won’t display. ideally, I would be able to not have the Segment field in the visual at all, but then be able to have a filter pane to select which filters to include, and it would aggregate conditionally by average or sum.

a. Create a parameter AvgOrFilter and add control to the sheet (Static List with two values Average, Sum
b. Change your calculated field as follows.

ifelse(${AvgOrSum} = “Average”,
avg(avgOver(
ifelse(in (METRIC, [‘Average ARR’, ‘Conversion to SQLs’, ‘Win Rate’]),
{PREVIOUS_QUARTER}, 0)
, [METRIC, SEGMENT, CATEGORY], PRE_FILTER)),
sum(sumOver({PREVIOUS_QUARTER}, [METRIC, SEGMENT, CATEGORY], PRE_FILTER))
)

The Sum and Avg in the calculated field will make your measure a Custom and no further selection is needed in the visual

Regards,
Giri

1 Like

Hi @jonfrank3366,
It’s been awhile since we last heard from you, did you have any additional questions or did you explanation provided above assist with your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

Hi @jonfrank3366,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!