cancel
Showing results for
Did you mean:  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

******* 1 ACCEPTED SOLUTION  Super User

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

Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!  5 REPLIES 5  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.

Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!    Helper V

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

Hope some wizard out there who can help me out 🙂  Super User

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

Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!    Helper V

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
*******************************************************  Helper V

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