Custom Measures denominator across whole dataset, rather than by segment

[KieranAdair]

Has anyone had much luck setting up something like this in Auto-Insights:

Let's say you've got a KPI based on a pass/fail rating for each record in your dataset, and the measure is a count of records that failed/count of all records.

Example: % Phone calls answered within 30 seconds

Custom Measure: Numerator = Count(# Record where call was answered after 30 seconds),  Denominator = Count(All records)*

*I've followed the guidance and set these up in a table in the following way with a Numerator column (1 if True) and Denominator column (1), then in AAI made the Customer Measure SUM(N)/SUM(D)

Record ID	Location	Answered in 30 seconds  YN	N Reason	Numerator	Denominator
a	Sydney	Y		0	1
b	Sydney	Y		0	1
c	Sydney	N	No available operators	1	1
d	Sydney	N	No available operators	1	1
e	Sydney	N	After hours call	1	1
f	Melbourne	Y		0	1
g	Melbourne	Y		0	1
h	Melbourne	N	After hours call	1	1

I can break this down across the Location segment: Sydney = 60% (3/5); Melbourne = 33% (1/3).

However, if I want to understand the N Reason Segment I get some weird results: No available operators = 100% (2/2); After hours call = 100% (2/2)

What I'd like is for the customer measure to take the count of the denominator from the entire table, while keeping the numerator as just the segment to get a balanced idea of the drivers of negative performance: No available operators = 20% (2/8), After hours call = 20% (2/8).

Anyone had luck configuring this? For now I've just set up a page in the mission to use count of trues rather than reporting on %s.

[bradshaw · Accepted solution]

Hi @KieranAdair, you should be able to get the overall percentage of "N Reason" by doing a query of "Number of Record ID" with breakdown of "N Reason" which will show that both "No operators available" and "After hours calls" account for 25% of total, with "Unknown" accounting for the other 50%. Although not sure whether this is any better than your solution of counting TRUE's.

[amelia_alteryx]

Hi @KieranAdair 

To add to Brad's response, if the numerator is relevant (i.e. you want "After hours call" AND numerator = 1), here's another way you can achieve the solution:

1. Create/convert your numerator into a segment. I called mine "Seg Numerator".
2.  Your Mission query bar will have "Number of Record ID" as the measure, and "After hours call" and "1" as the filters. The key call out in the totals view will give you the % proportion (25% in this scenario)
3. Repeat step 2 for "No available operators" and "1"

If the numerator isn't relevant or will always be "1", then Brad's solution is more efficient. Hope this helps!

Dataset:

Query bar:

 

 

Insight callout:

 

[KieranAdair]

Ah great, that makes a lot of sense - thank you Brad and Amelia!

I think the issue might be that I'm trying to have two pages which show something like this:

Page 1 - % Phone calls answered within 30 second

Date	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6
Result	10%	10%	10%	12%	15%	6%

Page 2 - Miss driver breakdowns

Date	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6
No Available Operators	6%	10%	8%	8%	10%	4%
After Hours Calls	4%	0%	2%	4%	5%	2%

And trying to display the WoW % change in drivers (ie. After Hours Calls increased by x points this week) rather than the WoW volume changes as the number of calls may differ.

I think though that this might not be an idea use case for AII at present, and that brad's solution to show the index count/breakdown might be best for now (have to filter out the blanks though otherwise the inverse metric (blank rows/not missed) dominates the visualization (as it'll be around 90% of the space with the drivers vying for the remaining 10%).

[KieranAdair]

Thank you for your replies Amelia and Brad!

I think the problem is that I'm trying to show percentage point movement for those drivers week to week, like this:

Page 1 - Overall Performance

Date	Week 1	Week 2	Week 3
Result	10%	12%	8%

Page 2 - Performance Drivers

Date	Week 1	Week 2	Week 3
No Available Operators	6%	6%	2%
After Hours Calls	4%	6%	6%

With AII picking up on the pp movement between the drivers week on week (ie. No Available Operators changed by 4pp this week), instead of the total volumes, as the volume of calls may differ over time.

That said, I'm thinking this may not be an ideal use case at the moment - and that Brad's solution (to show a raw count using the Number of Record ID) with the reason breakdown may be the best I can do at present. I have to filter out the blank rows though otherwise they dominate the visual.

[KieranAdair]

A bit of a workaround, but after playing around with this a bit more I've had some luck with One Hot Encoding the drivers too:

Record ID	Location	Answered  YN	N Reason	Numerator	Denominator	No available operator	After Hours Call
a	Sydney	Y		0	1	0	0
b	Sydney	Y		0	1	0	0
c	Sydney	N	No available operators	1	1	1	0
d	Sydney	N	No available operators	1	1	1	0
e	Sydney	N	After hours call	1	1	0	1
f	Melbourne	Y		0	1	0	0
g	Melbourne	Y		0	1	0	0
h	Melbourne	N	After hours call	1	1	0	1

Can't use these on the same axis, but at least I can then use these to get movement changes for specific segment breakdowns (ie. % of After hours call has increased in Sydney etc).