DAX measure to count values above average

pajarvey

New member
Joined
Nov 4, 2011
Messages
3
Reaction score
0
Points
0
I'm struggling with something that should be simple - I'm sure I'm missing something obvious:

I'm trying to count the number of values that are below the average (plus a little filtering).

I have one data set, with two columns

column1 || column2
6 || 1
5 || 1
7 || 1
6 || 1
5 || 2
7 || 2
6 || 2

I simply want a measure that will return the number of values in column1, for which two criteria are true:
  • [column2]=1
  • [column1] is above the average of values in [column1] for which column2 = 1
Should be simple, right? I'm pulling my hair out trying to write a CALCULATE measure that doesn't generate an error.
 

ruve1k

New member
Joined
Mar 22, 2011
Messages
4
Reaction score
0
Points
0
Try creating a calculated column in the table to hold the average of column1 where column2 = 1.
Code:
AvgCol1wCol2is1:=CALCULATE(AVERAGE(Table1[column1]),
                           ALL(Table1),Table1[column2]=1)
Then use that calculated column in your measure.
Code:
CountAboveAvg:=COUNTROWS(FILTER(Table1,
                           Table1[column1]>Table1[AvgCol1wCol2is1] 
                        && Table1[column2]=1))
 
Top