Excel Powerpivot measure conundrum- Average (of average?)

Question

I have a powerpivot table that shows work_tickets and timestamps for each step taken towards resolution:

`Ticket | Step | Time | **TicketDuration**
--------------------------------------
 1        1      5:30      15
 1        2      5:33      15
 1        3      5:45      15
 2        1      6:00      10
 2        2      6:05      10
 2        3      6:10      10

[ticketDuration] is a calculated column I added on my own. Now I'm trying to create a measure for the [AverageTicketDuration] so that it returns 12.5 minutes for the table above{ (15+10)/2 }. I haven't got a clue how to use DAX to produce the results. Please help!

Marcus Marcus · Accepted Answer · 2017-10-31T01:18:16

What you are looking for is the AVERAGEX function, which has the following definition AVERAGEX(<table>,<expression>)

The idea being that it will iterate though each row of a defined table applying your calculation, then average the results.

In the below example, I use Table1 as the table name.

To start with to iterate along tickets we would use the following VALUES( Table1[ticket]) which will return the unique values in the ticket column.

Then assuming that your ticket duration is always the same within a ticket ID, the aggregation method used in the expression would be Average(Table1[Ticket]). Since for example of ticket 1, (15 + 15 + 15)/3 = 15

Put together the measure would look like below:\

measure:=AVERAGEX( VALUES( Table1[ticket]), AVERAGE(Table1[Ticket Duration]))

The result when dropped into a pivot using your sample data.

Excel Powerpivot measure conundrum- Average (of average?)

1 Answers