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] ) ) )
Here's an example: https://www.dropbox.com/s/aszcfyjm0r5zn7a/Example.pbix?dl=0
Check the measure. I want to include the DISTINCTCOUNT of AreaId column and average on it.
Thanks for your help parry2k !
Hi,
Please explain the question is simple non technical language. What exactly are you trying to compute?
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 AreaIdI'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.
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 AreaIdYou can achieve with a table visual, just need to choose proper aggregation for each field.
Best regards,
Yuliana Gu
