Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

DAX Formula - group by

I'm working on a formula, for a calculated table, that will group the following columns:

 

Player, Year, Month (all part of a table called Results)

 

I then want to provide a sum of the total points within each grouping - in other words how many points did every player get each month.

 

My attempted formula is below, however this doesn't work for the reason "Function GROUPBY expects a column name as argument number 5"

 

PointsPerMonth = GROUPBY(Results,[Player],Results[Year],Results[Month],SUMX(Results,SUM(Results[Points])))

 

Can anybody help please?

 

The formula worked with just the groupings, its the SUMX/SUM formulas that have created the error.

Need Help
  • ibarrau's avatar
    ibarrau
    7 years ago

    It is great you found another way. But check your formula out, you are not writting the NAME:

     

    PointsPerMonth = GROUPBY(Results,[Player],Results[Year],Results[Month], "NAME", SUM(Results[Points]))

     

    Regards,

7 Replies

  • ofirk's avatar
    ofirk
    Resolver II
    7 years ago

    Hi,

    Are you creating a calculated column?

    Maybe this will work for you:

     

    PointsPerMonth = SUMX(FILTER(Results, [Player] = EARLIER([Player]) && [Year] = EARLIER([Year]) && [Month] = EARLIER([Month])), [Points])

  • ibarrau's avatar
    ibarrau
    Super User
    7 years ago

    Can you be more specific on what are you trying to do? for simple grouping you can just add a visualization like players and add the column points. The render will automatically group a SUM or what you want to agregate in the visualization by player o the category column you add on it. I am used to work with SUMMARIZE for grouping but it should be the same.

     

    The error in GROUPBY is that you have to put a "Name" (string) to the agregation as argument before the SUM. Replace this SUMX(Results,SUM(Results[Points])) for SUM(Results[Points]) because your are just doing an innecesary iteration. For example:

        GROUPBY (
            Product,
            'Product'[Category],
            'Product'[Subcategory],
            "Average Price", AVERAGEX ( CURRENTGROUP() , Product[Unit Price] )
        )

     

    Be carefull, this function returns a table not a value. I don't know if your objetive is to create a table or measure.

    Regards,

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I'm trying to create a a Calculated Table here (not a measure or column).

       

      I have tried using this formula instead, but receive the error "Function GROUPBY expects a column name as argument number 5."

       

      PointsPerMonth = GROUPBY(Results,[Player],Results[Year],Results[Month],SUM(Results[Points]))

       

      I have however tried a different approach to crating a calculated tabel, which was to create a measure as: SumPoints = SUMX(Results,[Points]) which has worked when adding this to a visualisation.

       

      Thanks everyone for your help & suggestions.

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Anonymous,

         

        It seems that you have solved your problem, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

         

        Best  Regards,

        Cherry

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Anonymous,

     

    Have you solved your problem?

     

    If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

     

    If you still need help, please share some data sample and your desired output so that we could help further on it.

     

    Best  Regards,

    Cherry