Skip to main content
cancel
Showing results for 
Search instead 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

Reply
flathead
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

GameIDKicksGoalsMisssesTackles
1AAA4157
1BBB6232
1CCC2141
2BBB7036
2CCC2224
2AAA1142

 

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?

IDKicksGoalsMisssesTackles
AAA5299
BBB13268
CCC4365

 

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
amitchandak
Super User
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])))

View solution in original post

2 REPLIES 2
flathead
Regular Visitor

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

 

Cheers,

amitchandak
Super User
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])))

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors