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
Super User
8 years ago

can you post sample data with expected result, it will help :)

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Please explain the question is simple non technical language.  What exactly are you trying to compute?

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

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