Average of Averages… no, seriuosly.

Tom, Bill and Sandra have one euro each; they go and buy some fruits.

Tom  buys 3 apples for 1€. Bill buys 2 oranges for 1€ and Sandra buys a coconut for 1€.
Tom says “apples cost 0,33 euro cents on average”.
Bill “oranges are 0,50 euro cents on average”
Sandra “coconuts are 1 euro on average”

What is the overall average price of fruits ?

I have seen more than once calculating this as:

0.33 + 0.50 + 1 = 1.83
1.83 / 3 = 0.61

Now, you are a Business Intelligence professional: imagine your customer asking you to calculate an average, such as the price per product grouped by product type and the total overall price per product at company level.
Next he dig into his laptop and proudly shows off his “treasure” Excel sheet that does it all, and by magic copy and paste computes the AVG() of the AVG()…

Unfortunately for your client, acrobatic math is not yet an Olympic discipline.

Tom, Bill and Sandra soon realize that they bought 3 + 2 + 1 = 6 pieces of fruit and they spent 3€, so a single piece is 0,50 euro cents.

Similarly your customer (after an intense discussion) will realize that for the last 15 years he used to calculate a price per product average “from a different point of view”. You may want to ease the pain by saying that it is a quite common error and he’s in good company: see Simpson’s paradox and the Berkeley gender bias case

In MicroStrategy terms this is what is called a “Smart Metric”.

When in the Metric Editor, switch to the Subtotals / Aggregation tab, down to the left there’s a check box that you can enable to surprise your customer… cool.

Leave a Reply

Your email address will not be published. Required fields are marked *