Attributing Change to Measures

[adammoo1984]

Good Morning,

I keep banging my head on my desk due to this calculation which in my head should be easy (but apparently it is not).

Given 3 measures based on 2 time periods, how can you attribute the amount of change to each measure.

The exact example is, given the transaction count, the average item price and the average items per transaction for this week and last week, how can we say that X amount of extra sales is due to the change in transaction count (or item price or items per transaction).

I thought it would be simply the case of base all metrics on the previous week except for the one you're wanting to see the change for, however I can never manage to balance the books.

Any help would be great, here is an example with some dummy data and my current formulas..

.

Thanks, Adam.

 

[JeffM]

It is easier to explain this in a somewhat simpler context.

a * u = p

b * v = q

where a and b are measurements or counts of attribute I, u and v are measures of attribute Ii, and p and q are measures of attribute III.

We want to explain the change between p and q in terms of the change between a and b and the change between u and v. I’ll follow a standard nomenclature for change.

[MATH]\Delta a = b - a \implies a + \Delta a = b.[/MATH] Delta a is the change from a to b.

[MATH]\Delta u = v - u \implies u + \Delta u = v .[/MATH] Delta u is the change from u to v.

[MATH]\Delta p = q - p \implies p + \Delta p = q. [/MATH] Delta p is the change from p to q.

[MATH]b * v = q \implies (a + \Delta a) * (u + \Delta u) =\\ a * u + a * \Delta u + u \Delta a + \Delta a * \Delta u =\\ p + a * \Delta u + u * \Delta a + \Delta a * \Delta u.[/MATH]And [MATH]\Delta p = q - p = a \Delta u + u \Delta a + \Delta a * \Delta u.[/MATH]
This is a bit messy and hard to explain, but it can be cleaned up in part by going to relative changes. We divide by p = a * u.

[MATH]\dfrac{\Delta p}{p} = \dfrac{a * \Delta u}{a * u} + \dfrac{u * \Delta a}{a * u} + \dfrac{\Delta a * \Delta u}{a * u} = \dfrac{\Delta a}{a} + \dfrac{\Delta u}{u} + \dfrac{\Delta a * \Delta u}{p}.[/MATH]
Multiply by 100 on both sides of the equation to get percentages.

The math is basic algebra, but it leaves us with a problem in presentation and explanation. We have three terms rather than the intuitively expected two terms.

The term [MATH]\dfrac{\Delta a}{a}[/MATH] measures what would have happened if only a had changed.

The term [MATH]\dfrac{\Delta u}{u}[/MATH] measures what would have happened if only u had changed.

The third term measures the correction needed if both have changed.