Microsoft Office Tutorials and References

In Depth Information

The classic formula for the standard deviation is the following:

We indicate with the average of the values and with the resulting value of the standard

deviation.

The problem with this formula is that you cannot compute it row by row because the

value of is an aggregate and cannot be used in a calculated column. Moreover, the value

of changes for each aggregate you compute. In the example in Figure 9-8, you can see

that there are three different values of , depending on the evaluation context: one for

each Category and one for the Grand Total. The average over all the values is different

from both averages over the two categories. So there is no way to compute the average

at the row level, and this brings us to an issue with the innermost term in our formula.

Luckily, the formula can be easily simplified, thanks to thousands of years of statistical

theory that is available to us, to this equivalent formula (the interested reader can look

at
http://en.wikipedia.org/wiki/Standard_deviation
for the details):

The big difference between the two formulas is that the average now appears outside the

loop that you need to compute the sum of all squares of values. Now you do not need to

know the value of the average at each row level. Instead, the only value you need at the

row level is the square of the value, which can be very easily computed by PowerPivot.

You can add a new calculated column to the PowerPivot table named Events, for which the

formula is the following:

ValueSquared = Events[Value] ^ 2

To compute the standard deviation now, you need to add a measure to the PivotTable. The

formula is the following:

StandardDeviation = SQRT( SUM( Events[ValueSquared] ) / COUNTROWS( Events )

- AVERAGE( Events[Value] ) ^ 2 )

The formula uses the COUNTROWS function, which counts the rows in a table and performs

the same computation shown before. With this new calculated measure, you can perform the

interesting analysis shown in Figure 9-9.