cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## Create a table based of an existing table, but sum the values, grouped by an ID Field

Hi all

Apologies if this already exists, I found some similar questions but not one that applies directly to my use case here.

Essentially I have 1 table that consists of data that is similar to the following example. The data is about statistics for a player (ID) for a particular season worth of games. Each row represents one players stats across a few categories for a particular game. there are multiple games in the data set

 Game ID Kicks Goals Missses Tackles 1 AAA 4 1 5 7 1 BBB 6 2 3 2 1 CCC 2 1 4 1 2 BBB 7 0 3 6 2 CCC 2 2 2 4 2 AAA 1 1 4 2

Is there a way to create a new table based off of the data above (I have approx 60 columns worth of player stats to use) where the values are summed for the player to summise the whole season? so it would look like the below?

 ID Kicks Goals Missses Tackles AAA 5 2 9 9 BBB 13 2 6 8 CCC 4 3 6 5

I understand I can use the summarise DAX function for one column, such as the following

=SUMMARIZE(Playerdatatable, 'ID', "SumKicksColumnName", Sum(Kicks))

However I am not sure how to effectively make this reusable for all the columns? and in addition to this there are a few columns in the table that I do not wish to be summed (they are either text or a different ID field) - but that is probably more of a secondary issue.

I am aware of how to do a measure within the existing table using Measure = MAXX(Values(playerdatatable[ID], Kicks), however I feel there might be a better way of doing things rather than creating 60 odd measures and the measure in this case provides a slightly different functionality.

I was investigating the use of ADDCOLUMNS and somehow integrating the use of ALLEXCEPT, but couldn't quite figure out the exact structure of the DAX query

Would the best way be to make a new column using the SUMMARIZE function and then just create a column for each field in that new table?

Any help would be greatly appreciated.

Cheers,

1 ACCEPTED SOLUTION
Super User

@flathead , You can use group by in-power query

https://docs.microsoft.com/en-us/power-query/group-by

for measure, you have to create like

MAXX(Values(playerdatatable[ID]),calculate(Max(playerdatatable[Kicks])))

2 REPLIES 2
Regular Visitor

@amitchandak thanks so much for that mate, game changer, really appreciate the assistance!

Cheers,

Super User

@flathead , You can use group by in-power query

https://docs.microsoft.com/en-us/power-query/group-by

for measure, you have to create like

MAXX(Values(playerdatatable[ID]),calculate(Max(playerdatatable[Kicks])))