Blog: Claudia Imhoff

« Travel warnings without trust | Main | Is open source BI for you? »

The problem with averages...

I can't tell you how many times BI designers have told me that their users want to see reports filled with averages -- average sales by store, average customer purchase, average inventory levels, and so on. Do people really understand how misleading and erroneous these figures can be? Here's my tip for you.

Average calculations are "smoothing" techniques; they remove the ups and downs of the actual data. They may be useful to give you a high level trend or estimation but they can be completely misleading if you base business decision on them. Let me give you an example of what I mean.

Suppose someone in charge of inventory levels asks for the average monthly sales for product A. The answer comes back that, on average, the company sells 100 units of Product A per month. Does that mean that there should be only 100 units and no more of Product A in inventory every month? I wouldn't bet my job on that...

The average is based on the fact that 1200 units of Product A sold over a 12 month period. This does not take into account the fact that the product's sales are seasonal, affected by marketing campaigns, or boosted by recurring external factors like sports events. In reality, Product A is quite seasonal with 80% (or 960 units) of its sales occurring in the summertime -- a three month period. In fact, the units may sell withing a 6 week period within even the 3 months. The rest are sold on either side of summer with no sales occurring in winter.

If the inventory were stocked at 100 units per month, there would not be enough available in the height of summer to sell so sales would be lost. Yet, the levels would be way too high in winter, causing unnecessary inventory carrying costs. That is the problem with averages...

In gathering requirements from your business users, make sure you dig a bit deeper into the users' needs than hearing just a cursory "I want the average sales amounts of..." It is always useful to ask why and how the data be used. It is wise to store the details that go into each of these average amounts in the warehouse to ensure that it remains possible to look at the actual numbers as well as averages.

I hope you find this tip useful. If you have others, please add them to this blog.

Yours in BI success,

Claudia

Share: