Highest average for all the rows DAX measure

[amalrio]

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

*******

[Greg_Deckler]

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

[amalrio]

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

[Greg_Deckler · 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])

[amalrio]

@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

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

[amalrio]

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

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