Averages from distinct count measures

Question

I am working with some customer interactions data and I am wondering if it is possible to calculate averages metrics based on a distinct count of an ID field. In my data set, most interactions will have multiple rows so I need to use the distinct count of the IDs to get these numbers.

Example 1: We have 4 sales territories and I'm hoping to compare the average interaction counts of each sales person in those territories. I can easily count the total interaction counts by territory or individual sales person but since the number of sales people varies depending on the territory, the total counts are skewed to point out the territory with the most sales people.

In the table below, I am trying to calculate the "Avg Interactions" column towards the right:

Example 2: I also am hoping to calculate the YTD average interaction counts by month or possible weeks. Here is a table using YTD numbers through the end of March with the two average calculations I am trying to calculate:

I appreciate any help people can provide.

dimkavid · Answer

Hi @phottovy

Adding on to what @kentT mentioned as the workaround. Using the multi query function will get you a high level average for the identified time period PLUS the previous period (see example screenshot). So you can get some understanding of averages over time. Seeing how you want to be able to see the averages for specific territories, I'd suggest adding an additional page for each territory in your Mission where both of the queries are filtered for a specific territory. That way you can go page by page to see the high level average for each territory.

kentT · Answer

Hi @phottovy

Thanks for the question. This is Kent from the Auto Insights product development team.

Based on your example, it looks like a mathematical formula for your calculation could be expressed as count_distinct(interaction) / count_distinct(salespeople), please let me know if this is correct?

Unfortunately we do not support this calculation at this stage, however, you could try to make use of Auto Insights' multi query function in search & missions as a workaround for part of the calculation (see screenshot on where the button is). One query could be number of interactions and another as number of sales person, then you could also select Territory as breakdown, or change the time granularity of the query. It is a workaround, as it only calculates the numerator and denominator, but not the average, neither does it calculate the monthly /weekly averages.

We have taken notes of this calculation and will consider this in our product roadmap.

Screen Shot 2023-04-24 at 2.27.19 pm.png