Count of Sites Where Average Audit Score is Greater Than . . .

[Luke_Howells]

Hi Everyone,

I am very new to Power BI so some of this stuff may seem very simple. I am trying to get a count of the number of sites where the average audit score is greater than 8.50. There are multiple entries for each site and the audit score column does contain blanks, if this makes any difference. I've added below a little snippet if it helps. I have had to blur out the site name for confidentiality reasons. 

Can someone help?

[v-eachen-msft]

Hi @Luke_Howells ,

At first, you need to create a measure to calculate average number based on site name.

Measure =
CALCULATE (
    AVERAGE ( 'Table'[Audit Score] ),
    FILTER (
        ALLEXCEPT ( 'Table', 'Table'[Site Name] ),
        'Table'[Audit Score] <> BLANK ()
    )
)

Then get the number with the condition ">=8.5".

Measure 2 =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Site Name] ),
    FILTER ( 'Table', [Measure] >= 8.5 )
)

[Greg_Deckler · Accepted solution]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second, you probably want:

Measure = 
  COUNTROWS(
    FILTER(
      SUMMARIZE(
        'Table',
        [Site Name],
        "__Average",AVERAGE('Table'[Audit Score])
      ),
      [__Average] > 8.5
    )
  )

[Watsky]

@Luke_Howells 

Assuming that Audit Score is a measure and you're looking to create a measure that gives you the count you could try this:

Measure = 
VAR AUD = [Audit Score]
RETURN
 CALCULATE(count([Site Name]),FILTER(Table Name,AUD > 8.50))

[JCPO]

Hi Luke,

I assume you need this type of table?

Steps: 

1. Click the Modelling Tab

2. Choose Create new Column

3. In the Dax Query, input this formula (very similar to Excel)

*Qualifier = if('Table'[Score]>8.5,"Passed",if('Table'[Score] = BLANK(),"No Data","Failed"))

4. Create a Matrix from the Visualizations pane. In the values row, drag each column to their respective fields.

Make sure to use distinct count for the values fields.