topic Re: GROUPBY DAX to return a table with a sum of column that group by 2 other columns from another table in DAX Commands and Tips

GROUPBY DAX to return a table with a sum of column that group by 2 other columns from another table

Anonymous — Mon, 29 Aug 2022 22:36:20 GMT

Hello wonderful community, I will try my best to be brief in this request.

For simplicity, I have a table below (the table name is WorkCentre220) that I have filtered Week Number = 38 and Year = 2022. The total sum of Required Hours under these filters is 98.698 (98.70 round off)

Challenge:

I want to return a new table using GROUPBY function that group [Week Number] and [Year] into a single row and sum the total value of column [Required Hours] into One Row.

I have worked out the DAX formula to return the table but can't quite figure out how to finish this:

Required_Hours_By_Week_And_Year = GROUPBY( WorkCentre220, WorkCentre220[5. Week Number], WorkCentre220[6. Year], "Total Required Hours by Week and Year", )

Expected Results:

Week Number	Year	Total Required Hours by Week and Year
38	2022	98.70
39	2022	264.90
40	2022	94.90

I'd really appreciate if someone could help me please.

Thanks everyone and have a wonderful day!

Re: GROUPBY DAX to return a table with a sum of column that group by 2 other columns from another table

Anonymous — Mon, 29 Aug 2022 22:55:22 GMT

Hi wonderful community, I have figured it out!!

Use SUMMARIZE instead of GROUPBY:

NewTable = SUMMARIZE(WorkCentre220,WorkCentre220[5. Week Number],WorkCentre220[6. Year],"Total",SUM(WorkCentre220[3. Required Hours]))

Results:

Re: GROUPBY DAX to return a table with a sum of column that group by 2 other columns from another table

v-yadongf-msft — Thu, 01 Sep 2022 03:02:23 GMT

Hi Anonymous ,

I am so glad to hear that your problem has been solved . Please consider Accept it as the solution to help the other members find it more quickly.

Best regards,

Yadong Fang