Sometimes dealing with data in Insights can cause confusion. One common source of discrepancies is calculating the total average of individually averaged values, also known as Simpson's paradox.
In most of the cases, the overall average of separately averaged values is not equal to the average of the original set of numbers. They can be equal only if all of the averaged values are computed over the numeric sets with the same number of values. However, in day-to-day reporting, chances that you’ll be looking at the datasets with exactly the same number of values is close to zero.
Let's take a look how discrepancy occurs:
Agent A solved five tickets with the following full resolution time: 3, 6, 4, 6, and 11. Agent B solved two tickets with full resolution time: 2 and 8.
Average full resolution time for the agent A is six hours, but for the agent B it is five hours. Average of these individual results equals to 5.5 hours. However, if we calculate total average of all tickets solved by both agents, we'll get 5.42 hours.
As you see, we got a discrepancy of 0.08 hours in this example, but in real life when we compute the total average for tens of assignees and hundreds of tickets the discrepancy will be much more substantial.
Using Rollup for computing the overall mean
If your report on average or median values and you need to add an overall mean to the table, use the Rollup operator.
Rollup calculates the metric's formula as if the attributes weren't in the report, which allows aggregating the actual total for the column of data.
For example, we have built a report that computes average, median, and percentage metrics and is sliced by the Ticket Assignee attribute. If we add Average and Rollup aggregators to this report, we'll get the following results:
As you can see, the discrepancy between Average and Rollup is huge in this case. The Rollup returns the real overall mean of each metric because Ticket Assignee attribute is not taken into consideration. The system simply calculates the following table in the background:
To add the Rollup to a report
Access an existing table report or create a new one.
Right-click a metric in the table header.
Select Totals > Rollup > of All Rows.