Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
)
)
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 )
)
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
)
)
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))
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |