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
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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

@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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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