The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
94 | |
86 | |
70 | |
65 |
User | Count |
---|---|
244 | |
127 | |
119 | |
81 | |
78 |