PowerPivot Averages

[PerryN1]

Hi all, I'm running Powerpivot in excel 2013 and have a table data whereby a list of people complete several tasks once per day, e.g.

Date | Day_ID | Person | Score1 | Score2 | Score3

08/02/14 | ND1 | Person1 | 10 | 20 | 20
08/02/14 | ND1 | Person2 | 13 | 16 | 21
08/02/14 | ND1 | Person3 | 7 | 26 | 31
01/02/14 | ND4 | Person1 | 15 | 21 | 23
01/02/14 | ND4 | Person2 | 25 | 16 | 21
01/02/14 | ND4 | Person3 | 65 | 21 | 14

I'm basically after calculating the individual average of each test for each Day_ID. For example Person1's average for Score1 on ND4 for the whole database (if that makes sense?). Any help greatly appreciated!

 

[scottsen]

Avg Score := DIVIDE(SUM(MyTable[Score1]) + SUM(MyTable[Score2]) + SUM(MyTable[Score3), 3 * COUNTROWS(MyTable))

 

[PerryN1]

Thanks but not quite what I'm after. I'm basically after the Score1_ND_Avg column below...and then will repeat for score2/score3 etc.

Date | Day_ID | Person | Score1 | Score2 | Score1_ND_Avg

08/02/14 | ND1 | Person1 | 10 | 20 | 15
08/02/14 | ND1 | Person2 | 13 | 16 | 14
08/02/14 | ND1 | Person3 | 7 | 26 | 9.5
01/02/14 | ND4 | Person1 | 15 | 21 | 12.5
01/02/14 | ND4 | Person2 | 25 | 16 | 20
01/02/14 | ND4 | Person3 | 65 | 21 | 38.5
25/01/14 | ND1 | Person1 | 20 | 10 |
25/01/14 | ND1 | Person2 | 15 | 13 |
25/01/14 | ND1 | Person3 | 12 | 10 |
24/01/14 | ND4 | Person1 | 10 | 12 |
24/01/14 | ND4 | Person2 | 15 | 13 |
24/01/14 | ND4 | Person3 | 12 | 10 |

 

[scottsen]

Just add a calc column of ([Score1] + [Score2]) / 2 ?

 

[PerryN1]

Forget that score2 even exists...

Date | Day_ID | Person | Score1 | Score1_ND_Avg

08/02/14 | ND1 | Person1 | 10 | 15
08/02/14 | ND1 | Person2 | 13 | 14
08/02/14 | ND1 | Person3 | 7 | 9.5
01/02/14 | ND4 | Person1 | 15 | 12.5
01/02/14 | ND4 | Person2 | 25 | 20
01/02/14 | ND4 | Person3 | 65 | 38.5
25/01/14 | ND1 | Person1 | 20 |
25/01/14 | ND1 | Person2 | 15 |
25/01/14 | ND1 | Person3 | 12 |
24/01/14 | ND4 | Person1 | 10 |
24/01/14 | ND4 | Person2 | 15 |
24/01/14 | ND4 | Person3 | 12 |

i.e. Person1 average for ND1 would be the average of 10 and 20 - 15.

 

[scottsen]

Typically, for averages I write one of these two measures:
MyAvg := AVERAGE(MyTable[Score1])
or
MyAvg := DIVIDE(SUM(MyTable[Score1]), COUNTROWS(MyTable))

And I suspect that will work fine for you as well. You may be trying to force the calculation into a calculated column instead of a measure, but I would want to know why...

 

[PerryN1]

Using =average(MyTable[Score1]) returns the average for all of the 'persons' for the whole table. The average needs to be filtered to the 'person' and also the 'ND'. I'm sure I explained this clearly enough in the previous posts!

 

[scottsen]

Your statement is technically true. However...

• [MyAvg] would also return the average for any ND, by putting ND on rows.
• ... would return the average for any Person, by putting person on rows
• ... would return the average for person and ND, but putting them both on columns/rows.
• ... would return the average per day, but... you guessed it... putting days on rows.

This is the beauty of measures... they are true regardless of the shape of your report.

 

[PerryN1]

Ah ok I understand where you're coming from, will have a play around. Thanks for your help!