Forum Discussion
Post PatronDAX - GROUP BY, MIN, AVERAGE
Hi,
I want to do the following but not sure if it can be done in one DAX Measure or a combination of DAX, Calculated Columns etc...
First of all I have two tables
Visits
EngineerCalendar
The Visits Table I have the following fields
EngineerId
ArriveDate
ArriveTimeSec
I want the MIN for ArriveTimeSec Group By EngineerId, ArriveDate
I then want to AVERAGE the above GROUP BY and convert the Field ArriveTimeSec to be the fomat of HH:SS
I have the below DAX but I cannot include the AVERAGE with the DAX formula failing
Avg 1st Arr Time =
VAR _AvgTime = CALCULATE(MIN(Visits[ArriveTimeSec]), CROSSFILTER(EngineerCalendar[CalendarKey], Visits[CalendarKey], Both))
VAR _Hrs = FLOOR(_AvgTime / 3600, 1)
VAR _Min = FLOOR(MOD(_AvgTime, 3600) / 60, 1)
RETURN
IF(
ISBLANK(_Hrs),
BLANK(),
RIGHT("0" & _Hrs, 2) & ":" & RIGHT("0" & _Min, 2)
)
Thanks
Hi AvPowerBI ,
I guess you need to add fields from different tables to the same visual, so it is necessary to establish a relationship between the three tables.
“
Also I then created the Calculated Column Avg_time, do I then have to create another calculated column to do the following
Format = FORMAT( [Avg_time], "HH:SS")
"
If you need the returned result of this calculated column, just create it, or you can combine this formula with the Avg_time formula.
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
- Greg_Deckler
Community Champion
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.
For Duration
You will need to convert those to seconds, add them and essentially convert them back more or less. Look at the links below, they should get you what you need.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Chelsie-Eiden-s-Duration/m-p/793639#M389
https://community.powerbi.com/t5/Community-Blog/Aggregating-Duration-Time/ba-p/22486
https://community.powerbi.com/t5/Quick-Measures-Gallery/Duration-to-Seconds-Converter/m-p/342279#M92 
v-lionel-msftCommunity Support
Hi AvPowerBI ,
Try to do like this.
1. Create a calculated table.
Table = SUMMARIZE( Visits, [EngineerId], [ArriveDate], "Min_time", MIN(Visits[ArriveTimeSec]) )2. Create calculated columns (You can combine the following two formulas.).
Avg_time = VAR x = AVERAGE( [Min_time] ) RETURN xFormat = FORMAT( [Avg_time], "HH:SS")Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
AvPowerBIHi v-lionel-msft ,
I get the below error when trying to create the calculated table
The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.
Thanks
- v-lionel-msft
Hi AvPowerBI ,
Did you create a table?
Best regards,
Lionel ChenIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
