Results 1 to 2 of 2

Thread: DAX measure to count values above average

  1. #1

    DAX measure to count values above average



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    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))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •