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!