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.
I'm trying to figure the DAX to get the count (or sum) of "High Average Scores" (in this case, an average >= 6) but dynamic based on the other columns in the table.
I was thinking I could use a measure to return 1 if "High Average Score", otherwise 0 and then count/sum.
High Avg Score = IF(AVERAGE('Table'[Score]) >= 6, 1, 0)
I've tried a second measure using x-functions for SUM and COUNT, but my average is by related column in the table not each row, so that doesn't seem right.
You can see in the red box the "total" is 0--I'm trying to get the count, which is numbered next to each box. Thanks for any help.
This approach works for a given column, thank you.
I figured I could create explicit measures for each column, but I was hoping the measure would be dynamic based on the other column(s) in the table.
If not possible, this method will surely work.
High Avg Score category new =
VAR newtable =
FILTER (
GROUPBY (
ALL ( 'Table' ),
'Table'[Category],
"@avg", AVERAGEX ( CURRENTGROUP (), 'Table'[Score] )
),
[@avg] >= 6
)
RETURN
IF (
ISFILTERED ( 'Table' ),
IF ( AVERAGE ( 'Table'[Score] ) >= 6, 1, 0 ),
COUNTROWS ( newtable )
)
Hi, @jeffshieldsdev
I think the reason is that the last row (total row) of each table visualization is considering the whole table. This means the whole table's average is 5.53 so that is why the counting of >=6 is always zero.
And each visualization is showing a different context. So, in my opinion, the different measure has to be applied to each of the visualizations.
I wrote three measures in the sample pbix file. please kindly check.
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |