DAX - GROUP BY, MIN, AVERAGE

[AvPowerBI]

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

[v-lionel-msft]

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
x

Format = FORMAT( [Avg_time], "HH:SS") 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AvPowerBI]

Hi @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 Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AvPowerBI]

Hi @Greg_Deckler ,

When I created the table will I have too manually join this back to one of the two 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")

As when I did that, the values were repeating the same per CalendarKey which I'm not if thats because the table is seprate and is not related?

Thanks

[v-lionel-msft · Accepted solution]

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 Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-lionel-msft]

Hi @AvPowerBI ,

Has your problem been solved?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

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