Reading timetoreply numbers in Power BI

The figures in the timetoreply OData feed are the same figures as on the report screens. But some of them can’t simply be added up or averaged in Power BI, the way most numbers can. This article explains which ones, and how to check a figure against timetoreply.

Team totals aren’t the sum of the mailbox rows

Take a team’s total from its summary row (SlaReport, OverviewReport or AwaitingResponseReport), never by adding up its rows in the …Mailboxes table.

One email sent to three people in a team is one email received by the team, and one on each person’s row. So the mailbox rows add up to three, but the team received one. The same applies to conversations, and to a mailbox with an alias: an email sent to both addresses is on both rows.

For the same reason, adding up team totals doesn’t give a company total if a mailbox is in more than one team. For a company total, use a MyCompany row.

If you show teams and their mailboxes in one matrix, use measures that read the summary table at team level and the mailbox table at address level, and leave the grand total blank.

Combine SLA percentages from the counts

Don’t average the percentage columns. Add up the counts, then divide:

Within SLA % = sum of Within SLA ÷ sum of SLA Eligible

For example, if one row has 9 of 10 replies within SLA (90%) and another has 1 of 2 (50%), the combined figure is 10 ÷ 12 = 83.33%, not the 70% you get by averaging.

Only combine rows that do not overlap, such as different weeks for the same team. Weighting does not remove duplicate replies across teams or mailbox addresses.

In DAX, for first replies:

Within SLA % = DIVIDE(SUM(SlaReport[FirstReplyTimeWithinSla]), SUM(SlaReport[FirstReplyTimeSlaEligible]))

• Eligible is within SLA plus breaches. Replies that are excluded from the SLA aren’t in it, so it isn’t the same as all replies.
• An empty percentage means nothing was eligible. That’s different from 0%, which means there were eligible replies and none were within the goal. DIVIDE keeps the difference. The report screens show an empty percentage as N/A.
• The feed’s own percentage columns are on a 0–100 scale. DIVIDE gives a 0–1 ratio, so format the measure as a percentage.

Awaiting-response counts are live

An awaiting-response count is how many conversations are still waiting for your reply at the time of the refresh. It isn’t how many were waiting at the end of that week.

• A past week’s count goes down as your team replies to old conversations.
• On older weeks, the 4, 6 and 8-hour counts are the same. Every conversation still waiting from a week that ended more than 8 hours ago has been waiting longer than all three thresholds. That’s expected.
• On the awaiting-response tables, a conversation belongs to the week of its newest email. If a customer follows up the next week, the conversation moves to that week.
• The thresholds are elapsed time, not business hours.
• Zero waiting doesn’t mean no email. Conversations may have been answered or closed.

Overview’s conversation status columns work the same way: they’re the status now, so past weeks change as conversations move on.

Because past weeks change, refresh the whole range every time. Incremental refresh isn’t supported.

Team membership has no history

The reports use today’s team members for every week, including past weeks. If someone joins a team, their past email is counted for that team in earlier weeks too, and if they leave, it isn’t. This is how the report screens work as well.

Rows are per email address

The …Mailboxes tables have one row per email address, not per mailbox. A mailbox with an alias can have two rows. To group them, relate MailboxId to Mailboxes[MailboxId], or look the address up in MailboxAddresses.

There’s only a row for an address with activity in the week, and on the awaiting tables, only for an address with something waiting. So an empty cell for an address usually means “nothing”, not an error. The report screens can list addresses with no activity, so they may show more rows than Power BI.

External and Internal

• External (EverybodyElse) is email with addresses outside your company’s domains. It matches Everybody else on the report screens.
• Internal (MyDomains) is email with addresses inside your domains, including addresses that aren’t monitored mailboxes. It matches My domains.

External plus Internal equals Anybody. Email that includes an internal contact isn’t counted as External, even if an external contact is on it too.

Dates, times and time zones

• Weeks run Monday to Sunday in the token owner’s timetoreply time zone, unless you set the timeZone report option. Every report row shows the zone it used in TimeZone. Power BI can convert a time, but it can’t move an email into a different week, so set the zone in timetoreply, not in Power BI.
• Only completed weeks. The current week appears once it’s finished, in that time zone.
• Timestamps are in UTC: EvaluatedAt on the report tables, LastReceivedAt on Conversations and SentAt on Messages.
• Converting to local time behaves differently once published. Power Query’s DateTimeZone.ToLocal and DateTime.LocalNow use your computer’s time zone in Desktop, but UTC in the Power BI Service. A converted time can look right in Desktop and shift by hours after a scheduled refresh. Leave the timestamps in UTC, or use DateTimeZone.SwitchZone only when one fixed offset is right for everyone reading the report. A fixed offset doesn’t change for daylight saving time, so the clock can be an hour out for part of the year. Power Query has no built-in conversion that follows daylight saving for a chosen time zone. See Microsoft’s DateTimeZone.ToLocal and DateTimeZone.SwitchZone references.

Check a figure against timetoreply

To compare a Power BI figure with the SLA report:

1. Refresh Power BI and open the SLA report at about the same time. Live counts change between the two.
2. Choose the same team and the same Monday to Sunday dates. Don’t use Past week: it’s the last 8 days up to today, not a calendar week.
3. Match the communication: Everybody else for External, My domains for Internal.
4. Match the settings: time zone, goals and Cc. Compare with the TimeZone and goal columns on the row. Clear any filters you didn’t use in Power BI.
5. For emails and SLA, leave No reply for off, then compare the First Reply or Overall Reply columns.
6. For an awaiting-response count, turn No reply for on at the same threshold. Then open Mailbox Leaderboard → Table Options and turn on Conversations → Awaiting Mailbox Response. Compare that column, not Conversations → Total. No reply for also narrows the report’s other figures, so don’t compare its email counts with your unfiltered ones.

If a figure still differs, note the team, week, communication, goals, time zone, Cc setting, threshold and EvaluatedAt, and contact us.

Need help or have feedback?

Email us at support@timetoreply.com. We’re happy to help.