Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

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

     

OwenAuger's avatar
OwenAuger
Super User
8 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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!