PowerPivot Grouped Average DAX

Question

I'm trying to model some outbound calling data in PowerPivot. We have reps across multiple locations, and in general we breakdown our outbound calling into two periods of the day (before and after 12pm).

We can export data from our phone system a list of every call made for a day -- let's say an example is as follows:

+------------+-------------+-------+-----------+-------------+
|    Date    | Call Length | Agent | Workgroup | Call Period |
+------------+-------------+-------+-----------+-------------+
| 01.01.2016 | 00:05:26    | Sam   | Sydney    |           1 |
| 01.01.2016 | 00:15:05    | Sam   | Sydney    |           1 |
| 01.01.2016 | 00:55:22    | John  | Sydney    |           2 |
| 01.01.2016 | 00:45:11    | Sam   | Sydney    |           2 |
| 01.01.2016 | 00:04:52    | John  | Sydney    |           1 |
| 01.01.2016 | 00:01:52    | Timmy | London    |           1 |
| 01.01.2016 | 00:02:21    | Timmy | London    |           2 |
| 01.01.2016 | 00:05:21    | Karen | London    |           1 |
| 02.01.2016 | 00:15:21    | Sam   | Sydney    |           1 |
| 02.01.2016 | 00:42:44    | Sam   | Sydney    |           2 |
| 02.01.2016 | 01:52:22    | John  | Sydney    |           1 |
| 02.01.2016 | 00:53:24    | John  | Sydney    |           1 |
| 02.01.2016 | 00:05:53    | Kerry | Sydney    |           2 |
| 02.01.2016 | 00:43:43    | Sam   | Sydney    |           2 |
| 02.01.2016 | 01:08:00    | John  | Sydney    |           2 |
| 02.01.2016 | 00:13:52    | Timmy | London    |           2 |
| 02.01.2016 | 00:25:44    | Timmy | London    |           1 |
| 02.01.2016 | 02:58:31    | Karen | London    |           1 |
| 02.01.2016 | 00:08:37    | Timmy | London    |           2 |
| 02.01.2016 | 00:12:28    | Karen | London    |           2 |
+------------+-------------+-------+-----------+-------------+

What I'm trying to calculate is the average daily time spent on phone per Workgroup, eg. on average how long is each agent on the phone at each location.

I'm guessing the arithmetic is as follows:

Measure 1: Total talk time for each Agent (eg. sum of all talk time for the day) Measure 2: Average agent total talk time per workgroup (eg. sum of the above grouped by workgroup, divided by number of agents in that workgroup)

The output might look something like this (but doesn't have to be):

+------------+-----------+-----------------------+-----------------+-----------------------------+
|    Date    | Workgroup | Total Number of Calls | Total Talk Time | Average Talk Time per Agent |
+------------+-----------+-----------------------+-----------------+-----------------------------+
| 01.01.2016 | Sydney    |                    11 | 03:02:42        | 1:34:53                     |
|            | London    |                     4 | 02:24:51        | 01:13:41                    |
| 02.01.2016 | Sydney    |                     5 | 01:52:05        | 00:56:51                    |
|            | London    |                    52 | 10:11:23        | 03:51:11                    |
+------------+-----------+-----------------------+-----------------+-----------------------------+

Apologies if I'm unclear it what I'm asking.

WimV WimV · Accepted Answer · 2016-04-28T16:18:29

Slicing your data on a pivot table will do the calculations. you only need the following calculations:

DurationOfCall  :=sum(MyTable[CallLength])

NrOfCalls   :=countrows(MyTable)

AvgDuration :=DIVIDE([DurationOfCall],[NrOfCalls])

this will give the following result (on your sample dataset):

Workbook with testcase: attachment

PowerPivot Grouped Average DAX

1 Answers