Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
reynolds_101
Regular Visitor

Percent of calls answered within x time KPI

Hi,

 

I am new to Power BI and want to learn how to make some dash boards for a telephone customer service desk. I have found building reports for number of calls per hunt group, call answer status etc all pretty simple.

 

The one key measure I cannot seem to display is Percent of calls answered within x time as a figure within the report. We have a column which shows the ring time of each call. All i need to do is a count of calls with a ring time of 20 seconds and under and compair this to the total count of ring times.

 

In excel this could be something like =(COUNTIF(E:E,"<=21")/COUNT(E:E))  with column E listing the ring time of each call. Any guidance would be great.

2 ACCEPTED SOLUTIONS

Hi @reynolds_101,

 

You can use the following measure to calculate the percentage as per your requirement

 

PercentCA = Var CountHits=CALCULATE(COUNT(Calls[Ringtime]),Filter(Calls,Calls[Ringtime]<21))
Var TotalCalls=Count(Calls[Ringtime])
Return 

CALCULATE(CountHits/TotalCalls)

Percent of calls.png

 

 

 

 

You can see the pbix file for your reference

To download the pbix click here

 

Please mark this post as an accepted solution if this helped you.

 

Regards,

Affan

View solution in original post

Hi @reynolds_101

You just need to click on the field in the field list and on the Modeling tab at the top select the % sign to change the field format.

For your help see the video below.

 

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

View solution in original post

12 REPLIES 12
affan
Solution Sage
Solution Sage

Hi @reynolds_101,

 

Can you please share a sample of your data and the expected result. The easiest to share your data is to copy and paste a cell range from excel like below

 

Category Item
BeveragesItem-007
BeveragesItem-008
BeveragesItem-009
BeveragesItem-010
BeveragesItem-011
BeveragesItem-012
BeveragesItem-013
BeveragesItem-014
BeveragesItem-015
BeveragesItem-016
DairyItem-017
DairyItem-018
DairyItem-019
DairyItem-020
DairyItem-021
DairyItem-022
DairyItem-023
DairyItem-024
Beauty productsItem-025
Beauty productsItem-026

Thanks for the reply Affan,

 

This is a sample from ring time collumn from the output. In this sample the % of calls answered within 20 seconds is 80% which would be the target KPI.

 

Ringtime
1
4
2
4
4
1
1
2
6
1
1
1
7
28
33
22
46
2
1
3

An update on this, I have added a new column that staes if the call answer time hit the KPI. Sample below, I now need a visual indicator that states the over all % of calls that hit KPI.

 

I am not sure how do turn this in to a gauge or similar on the report. Any help would be apreciated.

 

RingtimeKPI Hit
1Yes
4Yes
2Yes
4Yes
4Yes
1Yes
1Yes
2Yes
6Yes
1Yes
1Yes
1Yes
7Yes
28No
33No
22No
46No
2Yes
1Yes
3Yes

Hi @reynolds_101

Just modify affan's formula as below

PercentCA = Var CountHits=CALCULATE(COUNT(Calls[Ringtime]),Filter(Calls,[KPI Hit]="Yes"))
Var TotalCalls=Count(Calls[Ringtime])
Return 

CALCULATE(CountHits/TotalCalls)

 

Best Reagrds

Maggie

Hi @v-juanli-msft

what difference this will make in the result?

I have used calculation so that even if user does not add the KPI HIT column they get the desired result.

Regards

Hi @affan

I believe it is better to add "=" in your formula.

Filter(Calls,Calls[Ringtime]<=21)

 

Best Regards

Maggie

Hi @v-juanli-msft


The requirement mentione by @reynolds_101 is to measure the calls answered within "20" seconds, so using <=21 wouldn't make the correct calculaiton

 

Regards,

Affan

Hi Affan,

 

Thank you for your help, I have put this in to the report and ammended to the name of the input file and its all working with the other data.

 

The only issue I have is its not presented at a % figure but a decimal figure. Am I missing something obvious? It needs some general tidying but its coming together!

 

 

Hi @reynolds_101

You just need to click on the field in the field list and on the Modeling tab at the top select the % sign to change the field format.

For your help see the video below.

 

 

If this helped you, please mark this post as an accepted solution and like to give KUDOS .

 

Regards,

Affan

Thanks Affan,

 

Perfect, all working now.

Hi @affan

You are right, i' sorry i notice the "formula" instead of the statement.

 

Best Regards

Maggie

Hi @reynolds_101,

 

You can use the following measure to calculate the percentage as per your requirement

 

PercentCA = Var CountHits=CALCULATE(COUNT(Calls[Ringtime]),Filter(Calls,Calls[Ringtime]<21))
Var TotalCalls=Count(Calls[Ringtime])
Return 

CALCULATE(CountHits/TotalCalls)

Percent of calls.png

 

 

 

 

You can see the pbix file for your reference

To download the pbix click here

 

Please mark this post as an accepted solution if this helped you.

 

Regards,

Affan

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.