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

Average of max group by

Hello, 

 

I have a data set like this :

 

Name    Var1   

A            5

A            7

B             5

B             2

C            1

C            3

 

I have to calculate the average of the max of each name. But I must have just the mean value like the table below :

Name   Var1    AvgMax
A           7            5
B           5            5
C           3            5
 
I need to have the same value per name to insert this measure into a graph which will have a line representing the group average.
 
Could you help me ?
1 ACCEPTED SOLUTION

@Jeremy19 , Try like

calculate(averageX(values(Table[Name]), calculate(max(Table[Var1]))),all(Table))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

11 REPLIES 11
smpa01
Resident Rockstar
Resident Rockstar

@Jeremy19  I have done it like this

maxval := CALCULATE(MAX('Table'[Column2]),ALLEXCEPT('Table','Table'[Column1]))
maxvalfilter := IF(MAX('Table'[Column2])=[maxval],[maxval])
summaxval := CALCULATE(SUMX('Table',[maxvalfilter]),ALL('Table'))
count := CALCULATE(CALCULATE(COUNTROWS('Table'), FILTER('Table','Table'[Column2]=[maxvalfilter])),ALL('Table'))
average := DIVIDE([summaxval],[count])

Capture.PNG


New Animated Dashboard: Sales Calendar


Thanks for your help. But the solution does not take into account all the filters of the page like the group or the date.

 

Jeremy19_0-1612201927113.png

In my actual dataset the expected value here is 1174

smpa01
Resident Rockstar
Resident Rockstar

@Jeremy19  I wish you included that in your original question


New Animated Dashboard: Sales Calendar


amitchandak
Super User IV
Super User IV

@Jeremy19 , Please find the attached file after the signature.

Measure = calculate(averageX(values('Table'[Name]), calculate(max('Table'[Var1]))),all('Table'))


Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

It works well on this example but when I add a filter by selecting two names it keeps the value 5

I know sorry I thought that would not change the solution and I wanted to try to simplify the problem

amitchandak
Super User IV
Super User IV

@Jeremy19 , Try measures like

averageX(values(Table[Name]), calculate(max(Table[Var1])))

or

averageX(values(Table[Name]), calculate(max(Table[Var1]), allexcept(Table, Table[Name])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks @amitchandak . These are solutions that I have already tested but I still get:

Name   Var1    AvgMax
A           7            7
B           5            5
C           3            3

@Jeremy19 , Try like

calculate(averageX(values(Table[Name]), calculate(max(Table[Var1]))),all(Table))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

This time I have an identical value, but it is not the right result. This measure does not take into account different filters that I have such as the date or the positions of the players.

Just with a small change to adapt to the filters it was the right solution

 

calculate(averageX(values(Table[Name]), calculate(max(Table[Var1]))),ALLEXCEPT(Table,Table[Poste]Table[Date]))
 
Thanks !

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.