Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |