Calculate an average of wait times

[mennosteen]

I'm trying to calculate the average TTA of a TTA grouped by call_ID, but it won't show the average of the sum correctly. Furhermore I want to combine call_id if just one of the call_id has been 1 on ext.nAnswered. I tried this with a filter but the lines with ext.nAnswered =0 are removed. 

Data:

The measure I used shows the sumof a call_id: 

TTA_Bundel = SUMX(SUMMARIZE(Telefoon, Telefoon[Call_ID], Telefoon[TTA]), Telefoon[TTA])

The output is:

I need help with two issues: 

1) I want to show an average of the TTA_bundel but it only shows the sum in the end.

2) The input from TTA should only be filtered out if the Call_IDhas no ext.nAnswered. 

a) for example: 1001008566V0200605should not show 10,46 but 30,502

[v-lionel-msft]

Hi @mennosteen ,

You can do like this.

__Average = 
CALCULATE(
    AVERAGE(Sheet1[TTA]),
    ALLEXCEPT(Sheet1, Sheet1[Call_ID])
)

__One 1 = 
VAR x = 
CALCULATE(
    SUM(Sheet1[Answered]),
    ALLEXCEPT(Sheet1, Sheet1[Call_ID])
)
RETURN
IF(
    x = 1,
    SELECTEDVALUE(Sheet1[Call_ID]),
    BLANK()
)

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.

[amitchandak · Accepted solution]

@mennosteen , try like

TTA_Bundel = averagex(SUMMARIZE(Telefoon, Telefoon[Call_ID],"_1", Telefoon[TTA]),[_1])
TTA_Bundel = averagex(SUMMARIZE(Telefoon, Telefoon[Call_ID],"_1", sum(Telefoon[TTA])),[_1])

[Greg_Deckler]

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