cancel
Showing results for
Did you mean:
Helper V

## DAX calculation max by group

Hi,

I am looking for healp with modeling in PowerBI model for case shown below.

In model I have two dimension tables Users and Domains , one fact table Evaluation for Users by Skills.

I am looking for DAX solution which will fill column in Users table with the highest Average of Score for SkillCategory like Analytics or Security.

Lets assume I have measure AvaregeScore =  AVERAGEG('Evaluation'[Score]) which calculates Average for each user of its score.

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft

## Re: DAX calculation max by group

Hi @Marcin,

Add below calculated columns in 'Evaluation' table.

```Average =
CALCULATE (
AVERAGE ( Evaluation[Score] ),
ALLEXCEPT ( Evaluation, Users[UserID], Evaluation[Skill] )
)

Max average =
CALCULATE (
SELECTEDVALUE ( Domian[SkillVCategory] ),
FILTER (
ALLEXCEPT ( Evaluation, Evaluation[Userid] ),
Evaluation[Average]
= CALCULATE (
MAX ( Evaluation[Average] ),
ALLEXCEPT ( Evaluation, Evaluation[Userid] )
)
)
)```

Then, create column in 'Users' table with this DAX formula.

```Hightest score skill =
LOOKUPVALUE ( Evaluation[Max average], Evaluation[Userid], Users[UserID] )```

Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Super User IV

## Re: DAX calculation max by group

This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

Also, if you could post your example data in a form that can be copied and pasted that would assist greatly. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

---------------------------------------

Not the Power BI thought police...

##### I have NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Datanaut!

Helper V

## Re: DAX calculation max by group

Thank you for links, unfortunately I stiill don't know how to get that SkillCategory that I am looking for ( Category not Score Value)

I will be gratefull for help with this.

Microsoft

## Re: DAX calculation max by group

Hi @Marcin,

Add below calculated columns in 'Evaluation' table.

```Average =
CALCULATE (
AVERAGE ( Evaluation[Score] ),
ALLEXCEPT ( Evaluation, Users[UserID], Evaluation[Skill] )
)

Max average =
CALCULATE (
SELECTEDVALUE ( Domian[SkillVCategory] ),
FILTER (
ALLEXCEPT ( Evaluation, Evaluation[Userid] ),
Evaluation[Average]
= CALCULATE (
MAX ( Evaluation[Average] ),
ALLEXCEPT ( Evaluation, Evaluation[Userid] )
)
)
)```

Then, create column in 'Users' table with this DAX formula.

```Hightest score skill =
LOOKUPVALUE ( Evaluation[Max average], Evaluation[Userid], Users[UserID] )```

Best regards,
Yuliana Gu
Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors