Microstrategy Blog

The “avg” function is MicroStrategy has caused me problems in the past. It’s not that “avg” does anything wrong…it just doesn’t necessarily provide the correct subtotals to the end user, depending on the end user requirements.

Here’s the report output that examine all of the different ways average can function.

Now we’ll examine these metrics left to right.

This first one is your true “Avg” metric. Here’s the SQL that is sent through when you query the Warehouse.

SELECT
- ID,
- AVG(VALUE)
FROM FACT_VALUE
GROUP BY ID


Simple enough…but now say you want the “total” average as related to these groups. In the metric definition if you select “avg” as the aggregation type, you’ll notice that the average in the subtotal, is simply the average of the averages (following me?). Because you’re returning the data already at the aggregated level, the system doesn’t know what’s driving t in the background.

The second metric is actually the “weighted average” for our purposes. This metric is actually defined as the SUM(VALUE)/COUNT(VALUE), with the Smart Metric Enabled on your metric creation. The subtotal of this metric is actually the true average of all of the values, not the average of the average.

The columns that follow prove the logic out. You can see that the sum/count is the “more” correct way to calculate average. Keep this in mind when designing your metrics.