cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
amalrio
Helper V
Helper V

Highest average for all the rows DAX measure

Hi Gurus, 

 

I need to creat a measure that gets the max average for all the site locations

in the below case I need the average for all the sites to be 7212 regardless of the Location.Site. 

this average gets calculated by dividing the Actual measure dividing by Months ( that is current monts of the year)

any help would be really appriciated. 

measure for actual is as below.

******

VAR Actual =
CALCULATE(
SUM(CONFIRMED_EXPIT_OM_ORDW[t Material Moved Ex Pit Truck Factor])
)
VAR Result = DIVIDE(Actual, 1000)
RETURN
Result

 

*******

 

amalrio_1-1631879451958.png

 

 

1 ACCEPTED SOLUTION

@amalrio So this doesn't work?

Average Measure =
  VAR __Table = SUMMARIZE(ALL('Table'),[Location.Site],"__Actual",[Actual])
  VAR __Max = MAXX(__Table,[__Actual])
RETURN
  DIVIDE(__Max,[Months])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@amalrio 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

The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

@Greg_Deckler ,

 

thansk for the reply, but it does not seems to produce the expceted result,

Hope some wizard out there who can help me out 🙂

 

 

@amalrio So this doesn't work?

Average Measure =
  VAR __Table = SUMMARIZE(ALL('Table'),[Location.Site],"__Actual",[Actual])
  VAR __Max = MAXX(__Table,[__Actual])
RETURN
  DIVIDE(__Max,[Months])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

View solution in original post

@Greg_Deckler ,

 

Thanks a lot for the prompt reply, how could I ingest YTD filter tabel into this calculation, it seems to pickup the entire dataset (as we are clearing fliters with ALL ?). I tried your table (__Table) ingets with below YTD date set, it does not seem to do the trick.

 

see below my code. Thanks heaps taking this far.

 

**************************************************

Test2 =
VAR startt = STARTOFYEAR( 'DATE'[Date])-1
VAR endd = ENDOFMONTH( 'DATE'[Date] )
VAR NumMnth = DATEDIFF( startt, endd, MONTH )
VAR CurrentDate = TODAY()
VAR DatesForFilter =
FILTER (
ALL('DATE'[Date])
,'DATE'[Date] <= CurrentDate && YEAR ('DATE'[Date]) = YEAR ( CurrentDate)
)
VAR __Table = SUMMARIZE(ALL(CONFIRMED_EXPIT_OM_ORDW),MINE_SITES_ORDW[Location.Site],"__Actual",[Actual_Ex-PitOM])
VAR __Table1 = CALCULATETABLE(__Table,DatesForFilter)
VAR __Max = MAXX(__Table1,[__Actual])
VAR Result = DIVIDE(__Max,NumMnth)
RETURN
Result
*******************************************************

Hi @Greg_Deckler ,

 

Instead of ALL, I wrap the selection around ALLSELECTED instead. that seems to be worked for me as I expected. I will accept your post as a solution since you gave me the direction needed, (maybe you can update your code with ALLSELECTED Instead for clarity. 

 

Thanks a lot WIZARD :)..

 

Below is my code that worked for me..

 

*********************************************************

Test2 =
VAR startt = STARTOFYEAR( 'DATE'[Date])-1
VAR endd = ENDOFMONTH( 'DATE'[Date] )
VAR NumMnth = DATEDIFF( startt, endd, MONTH )
VAR CurrentDate = TODAY()
VAR DatesForFilter =
FILTER (
ALL('DATE'[Date])
,'DATE'[Date] <= CurrentDate && YEAR ('DATE'[Date]) = YEAR ( CurrentDate)
)
VAR __Table = SUMMARIZE(ALLSELECTED(CONFIRMED_EXPIT_OM_ORDW),MINE_SITES_ORDW[Location.Site],"__Actual",[Actual_Ex-PitOM])
VAR __Table1 = CALCULATETABLE(__Table,DatesForFilter)
VAR __Table2 =
FILTER(
SUMMARIZE(ALL(CONFIRMED_EXPIT_OM_ORDW),MINE_SITES_ORDW[Location.Site],"__Actual",[Actual_Ex-PitOM])
,DatesForFilter
)
VAR __Max = MAXX(__Table,[__Actual])
VAR Result = DIVIDE(__Max,NumMnth)
RETURN
Result
************************************************************

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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!