Distinct Count and count the ones with an average Score of >= 90%

[MacyMolly]

Hi All, 
I'm new to powerbi. I'm trying to create a measure that will only count with Score(% Score) average of >=90% by ID. A distinct value on column "ID". Kindly refer to the attached image. Your help is greatly appreciated.

[Nathaniel_C]

Hi @MacyMolly , and if you use essentially the same measure, but just return the _calc, you will have the averages.

myMeasureAverage = 
VAR _curID =
    MAX ( 'Table'[ID] )
VAR _calc =
    CALCULATE (
        AVERAGE ( 'Table'[%Score] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), MAX ( 'Table'[ID] ) = _curID )
    )
RETURN
    _calc

Bonus! 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[MacyMolly]

Hi @Nathaniel_C, I'm sorry. I think I wasn't able to express myself very well. Well, I only want to create a measure that will count of all the distinct ID that able to achieved an average score of >= 0.90. I have a filter that can select multi weeks.
1. distinctcount ( ID)
2. only count each distinct ID if they passed the score of >= .90
3. Get the passing rate: total count of distinct ID who passed divided by the distinctcount(ID)

[Nathaniel_C · Accepted solution]

Hi @MacyMolly  try this:

NewMeasure =
VAR _distinctCount =
    DISTINCTCOUNT ( 'Table'[ID] ) //Count how many ids
VAR _summarizeTable =
    SUMMARIZECOLUMNS (
        'Table'[ID],
        'Table',
        "Average",
            IF ( AVERAGE ( 'Table'[%Score] ) >= .9, 1, 0 ) //if equal to or greater than .9 give it a 1
    )
VAR _passingCount =
    SUMX ( _summarizeTable, [Average] )
VAR _calc =
    DIVIDE ( _passingCount, _distinctCount ) //Sum the average column from the table above and divide it by the total of ids
RETURN
    _calc

and if you just want the passing count, return _passingCount instead of _calc
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[MacyMolly]

You Rock! Thank you @Nathaniel_C

[Nathaniel_C]

Hi @MacyMolly , you are welcome!

Nathaniel

[Nathaniel_C]

Hi @MacyMolly ,

myMeasurecount =
VAR _curID =
    MAX ( 'Table'[ID] )
VAR _calc =
    CALCULATE (
        AVERAGE ( 'Table'[%Score] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[ID] ), MAX ( 'Table'[ID] ) = _curID )
    )
RETURN
    IF ( _calc >= .90, 1, 0 )

 Try this.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel