cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mizaskun
Advocate I
Advocate I

Summarize by three columns

Good morning,

 

I have the following table:

 

USER  |  YEAR  |  REGION  |  RISK  |  VALUE

A  |  2019  |  EUROPE  |  A  |  2

B  |  2019  |  EUROPE  |  A  |  3

C  |  2019  |  EUROPE  |  A  |  4

A  |  2019  |  CHINA  |  A  |  1

B  |  2019  |  CHINA  |  A  |  3

C  |  2019  |  CHINA  |  A  |  2

A  |  2019  |  EUROPE  |  B  |  5

B  |  2019  |  EUROPE  |  B  |  1

C  |  2019  |  EUROPE  |  B  |  2

A  |  2019  |  CHINA  |  B  |  3

B  |  2019  |  CHINA  |  B  |  4

C  |  2019  |  CHINA  |  B  |  1

 

And I would like to get the average of the values by Year, Region and Risk in a calculated table. The result should be:

 

YEAR  |  REGION  |  RISK  |  AVERAGE

2019  |  EUROPE  |  A  |  3

2019  |  EUROPE  |  B  |  2,66

2019  |  CHINA  |  A  |  2

2019   |  CHINA  |  B  |  2,66

 

Thank you so much!!

 

 

1 ACCEPTED SOLUTION

@mizaskun 

Probably best to do this in PQ. If you want it in DAX:

NewTable =
ADDCOLUMNS (
    SUMMARIZE ( Table1, Table1[YEAR], Table1[REGION], Table1[RISK] ),
    "@Average", CALCULATE ( AVERAGE ( Table1[Value] ) )
)

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

 

Picture3.png

 

https://www.dropbox.com/s/w38enf8sb5ysogt/mizaskun.pbix?dl=0 

 

 


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

AlB
Super User
Super User

Hi @mizaskun 

1. Place YEAR, REGION and RISK in a table visual. Make sure theya re all set to "Don't summarize"

2. Place Value in the viusla and select average. Alternatively, you can create an explicit measure and add it to the table visual:

Average_M =

AVERAGE (Table1[Value])

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Good morning,

 

But I need to create a new table, not in a measure. That is the point.

 

Thank you! 

@mizaskun 

Probably best to do this in PQ. If you want it in DAX:

NewTable =
ADDCOLUMNS (
    SUMMARIZE ( Table1, Table1[YEAR], Table1[REGION], Table1[RISK] ),
    "@Average", CALCULATE ( AVERAGE ( Table1[Value] ) )
)

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!