Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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)
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
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
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 |
Beverages | Item-007 |
Beverages | Item-008 |
Beverages | Item-009 |
Beverages | Item-010 |
Beverages | Item-011 |
Beverages | Item-012 |
Beverages | Item-013 |
Beverages | Item-014 |
Beverages | Item-015 |
Beverages | Item-016 |
Dairy | Item-017 |
Dairy | Item-018 |
Dairy | Item-019 |
Dairy | Item-020 |
Dairy | Item-021 |
Dairy | Item-022 |
Dairy | Item-023 |
Dairy | Item-024 |
Beauty products | Item-025 |
Beauty products | Item-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.
Ringtime | KPI Hit |
1 | Yes |
4 | Yes |
2 | Yes |
4 | Yes |
4 | Yes |
1 | Yes |
1 | Yes |
2 | Yes |
6 | Yes |
1 | Yes |
1 | Yes |
1 | Yes |
7 | Yes |
28 | No |
33 | No |
22 | No |
46 | No |
2 | Yes |
1 | Yes |
3 | Yes |
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
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)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |