Count of High Average Scores

[jeffshieldsdev]

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.

Count of High Average Scores Question_2021-04-02_1419.pbix

[jeffshieldsdev]

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 )
    )

[Jihwan_Kim]

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.

https://www.dropbox.com/s/2e38ydyvin70fo7/Count%20of%20High%20Average%20Scores%20Question_2021-04-02... 

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.