Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi PowerBI experts! How do you distinct count a field based on a criteria on another columns' aggregation?
example:
StaffID | Score |
101 | 56 |
101 | 43 |
102 | 12 |
101 | 15 |
103 | 43 |
104 | 31 |
104 | 25 |
102 | 19 |
101 | 23 |
101 | 16 |
Desired output:
Distinct Count of StaffID where Average of Score is greater than or equal to 20.
Thanks in advance
Solved! Go to Solution.
@Anonymous,
Try this measure. The VALUES function returns distinct StaffID, enabling COUNTROWS to return distinct count.
Distinct Count StaffID =
VAR vTable =
ADDCOLUMNS (
VALUES ( Table1[StaffID] ),
"@Average", CALCULATE ( AVERAGE ( Table1[Score] ) )
)
VAR vResult =
COUNTROWS ( FILTER ( vTable, [@Average] >= 20 ) )
RETURN
vResult
Proud to be a Super User!
hi @Anonymous
you can also create a measure with this:
Avg20PlusCount =
VAR _table =
ADDCOLUMNS(
VALUES(TableName[StaffID]),
"ScoreAvg",
CALCULATE(AVERAGE(TableName[Score]))
)
RETURN
COUNTROWS(
FILTER(
_table,
[ScoreAvg]>=20
)
)
it worked like this:
@Anonymous,
Try this measure. The VALUES function returns distinct StaffID, enabling COUNTROWS to return distinct count.
Distinct Count StaffID =
VAR vTable =
ADDCOLUMNS (
VALUES ( Table1[StaffID] ),
"@Average", CALCULATE ( AVERAGE ( Table1[Score] ) )
)
VAR vResult =
COUNTROWS ( FILTER ( vTable, [@Average] >= 20 ) )
RETURN
vResult
Proud to be a Super User!