Power Pivot in Excel and Count the number of entries above average

[DrDebit]

If I have a table of numbers, called scores: 1...10. They are in the data set called Tests.

I have the following calculated fields:

Count Tests:=COUNTROWS(Tests) ... =10
Average Tests:=AVERAGE(Tests[Scores]) ... 5.5
Count Tests Above Average:=CALCULATE([Count Tests],Tests[Scores]>5.5) ... 5

All good so far, but if I change the last calculated field:

Count Tests Above Average 2:=CALCULATE([Count Tests],Tests[Scores]>[Average Tests]) ... I get an error message.

Is there an easy solution to this?

Thank you!

 

[RoryA]

You could use:

Count Tests Above Average:=VAR AveMeasure=[Average Tests] RETURN Countrows(FILTER(Tests,Tests[Scores]>AveMeasure))

 

[DrDebit]

Thank you, Rory. Do you have to use VAR, or is there another way?

 

[RoryA]

You could use AVERAGE directly in the CALCULATE filter criteria rather than supplying a measure.

 

[DrDebit]

Yes...I think that I tried it and it didn't work.

Count Tests Above Average 2:=CALCULATE([Count Tests],Tests[Scores]>[Average Tests]) ... I get an error message.

Again, many thanks!

 

[RoryA]

No, that's using the measure you created. I mean using:

=CALCULATE([Count Tests],Tests[Scores]>AVERAGE(Tests[Scores]))

 

[DrDebit]

I get an error: Semantic Error: A function "AVERAGE' has been used in a true/false expression that is used as a table filter expression. This is not allowed.

...but Count Tests ABove Average 3:=CALCULATE([Count Tests],filter(Tests,Tests[Scores]>AVERAGE(Tests[Scores]))) works.

What I don't understand is if I calculate the Average Score and call it AVerage Tests (as I did above), you can't use that. However, if I do the formula for average inside the function it works.

Thank you, Rory.