Forum Discussion
Use DISTINCTCOUNT in a GROUPBY expression
Hello! I've been trying to create a GROUPBY expression and one of the columns should be a DISTINCTCOUNT expression for the given group. However I get a message saying something like "CURRENTGROUP mus...
Anonymous
If you want to "get distinct ItemId per area and then average that" then I would suggest something like this:
Average Distinct Items by Area = AVERAGEX ( VALUES ( Sheet1[AreaId] ), CALCULATE ( DISTINCTCOUNT ( Sheet1[ItemId] ) ) )
Thanks for your reply parry2k.
ItemId can be repeated for the same AreaId. I want to get distinct ItemId per area and then average that.
This is dummy data, but close to my real requirements.
The point is, I need to use DISTINCTCOUNT inside a GROUPBY expression and I can't figure out how.
OwenAuger
Super User
Super UserAnonymous
If you want to "get distinct ItemId per area and then average that" then I would suggest something like this:
Average Distinct Items by Area =
AVERAGEX (
VALUES ( Sheet1[AreaId] ),
CALCULATE ( DISTINCTCOUNT ( Sheet1[ItemId] ) )
)
Thanks OwenAuger this will do the trick. But, it is still too weird to me that I cannot use DISTINCTCOUNT in a GROUPBY expression.
Thanks to everyone else for trying!