topic Re: Distinct Count and count the ones with an average Score of >= 90% in DAX Commands and Tips

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

Anonymous — Tue, 01 Jun 2021 00:12:19 GMT

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.

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

Nathaniel_C — Tue, 01 Jun 2021 01:17:33 GMT

Hi Anonymous ,

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

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

Nathaniel_C — Tue, 01 Jun 2021 01:24:32 GMT

Hi Anonymous , 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

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

Anonymous — Tue, 01 Jun 2021 17:51:15 GMT

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)

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

Nathaniel_C — Wed, 02 Jun 2021 00:12:52 GMT

Hi Anonymous 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

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

Anonymous — Wed, 02 Jun 2021 19:03:02 GMT

You Rock! Thank you Nathaniel_C

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

Nathaniel_C — Wed, 02 Jun 2021 19:34:00 GMT

Hi Anonymous , you are welcome!

Nathaniel