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,
