DAX Formula - group by

[PetyrBaelish]

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.

[v-piga-msft]

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

[ibarrau]

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,

[PetyrBaelish]

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.

[ibarrau · Accepted solution]

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]

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

[v-piga-msft]

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

[ofirk]

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