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] ) ) )
Hi Ashish_Mathur thanks for your reply.
If you take a look at the Measure field from my example, I want to include a column as a result of the group by that would be a distinct count from ItemId. This is what I have tried:
Measure = AVERAGEX(GROUPBY(Sheet1;Sheet1[AreaId];"Sum";SUMX(CURRENTGROUP(); [Number]); "DistinctCount"; DISTINCTCOUNT(Sheet1[ItemId])); [Sum])
However, the error that I get is: "Function 'GROUPBY' scalar expressions have to be Aggregation functions over CurrentGroup(). The expression of each Aggregation has to be either a constant or directly reference the columns in CurrentGroup()."
As you can see, one column of the GROUPBY result is "Sum" and uses SUMX with CURRENTGROUP and that works. However, I want a second column that will do DISTINCTCOUNT of ItemId for each AreaId.
In SQL I would write:
SELECT AreaId, SUM(Number), COUNT(DISTINCT ItemId)
FROM table
GROUP BY AreaId
I'm not sure if I can make it more clear then this :)
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