cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
PetyrBaelish Regular Visitor
Regular Visitor

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ibarrau Established Member
Established Member

Re: DAX Formula - group by

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 7
ofirk Regular Visitor
Regular Visitor

Re: DAX Formula - group by

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 Established Member
Established Member

Re: DAX Formula - group by

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,

Community Support Team
Community Support Team

Re: DAX Formula - group by

Hi @PetyrBaelish,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PetyrBaelish Regular Visitor
Regular Visitor

Re: DAX Formula - group by

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.

Community Support Team
Community Support Team

Re: DAX Formula - group by

Hi @PetyrBaelish,

 

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

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ibarrau Established Member
Established Member

Re: DAX Formula - group by

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,

PetyrBaelish Regular Visitor
Regular Visitor

Re: DAX Formula - group by

Thanks for your response - my last post wasn't clear - in trying to do something else I created a calculated column called SumPoints using the formula above and used that instead of my calcualted table I was previously trying (looking back, I can't remember why I was going down the calculated table route in the first place).