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 :)
Hi Anonymous,
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
You can achieve with a table visual, just need to choose proper aggregation for each field.
Best regards,
Yuliana Gu