cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jkilb
Helper III
Helper III

Groupby Duplicate ID and Average all other Columns

Hi, I would like to groupby [ID], which has multiple rows per [ID]. When grouping, I would like the rest of the columns to remain, but to average the values that exist for each duplicate [ID] row.

 

Here is an example. 

 

Thanks in advance, I appreciate your continued support! 

 

Current format: 

 

IDcolumn1column2
1105
12015
28030
29040

 

Goal

IDcolumn1column2
11510
28535

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture2.png


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Picture2.png


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

View solution in original post

Jihwan_Kim
Super User
Super User

Hi,

For creating a new table, please try the below.

 

Picture1.png

 

New Table =
SUMMARIZECOLUMNS (
'Table'[ID],
"column1", AVERAGE ( 'Table'[column1] ),
"column2", AVERAGE ( 'Table'[column2] )
)

 

 


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

smpa01
Super User
Super User

Measure 4 = AVERAGE('Table 1'[column1])
Measure 5 = AVERAGE('Table 1'[column2])

 

smpa01_0-1632886226226.png

@Jkilb 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


I'm sorry I should have mentioned. I would like to do this in the power query/transform data query editor. 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.