Forum Discussion
Use DISTINCTCOUNT in a GROUPBY expression
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] ) ) )
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.
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.
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 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!
can you provide what you are expected to be output of sample data you shared in pbix
is this the output you are expecting from your sample data