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

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
8 years ago

just wondering why you need to do group by, if you drop areaid, number and itemid, you can use sum aggregation for number and distinct count aggregation for itemid, and you will get the result.

 

 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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.