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.
Search JabSto ::

Custom Search