Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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))

View solution in original post

11 REPLIES 11
smpa01
Super User
Super User

@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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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

@Jeremy19  I wish you included that in your original question

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
amitchandak
Super User
Super User

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

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

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
Super User

@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])))

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))

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.