Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi Team,
I've got a column that contains all the phone calls taken by agents for the month. I would like to create a column caled 'Average' which calculates the average percentage of calls taken by each agents.
Ideally the formula would be total queue call taken by agent / total number of queue calls taken by all agents
However, when I use the Average DAX function in power BI, it return the same value (see image below)
Please advise what I'm doing wrong.
Assuming the total number of calls is 2880, the result I want is:
Solved! Go to Solution.
Hi @Anonymous
Please find attached the dropbox link with the PBIX file.
The SubAvg column is the final result that you are looking for.
https://www.dropbox.com/s/a52hlwjy7nhqu8w/SD%20Monthly%20Reporting.pbix?dl=0
Hi @Anonymous
please try
=
DIVIDE (
SUM ( 'Service Desk Monthly Report'[Queue Call] ),
CALCULATE (
SUM ( 'Service Desk Monthly Report'[Queue Call] ),
ALL ( 'Service Desk Monthly Report'[Agent] )
)
)
@Anonymous
Tha is extremely strange!!! Would you please create a seperate measure
Total Calls = SUM ( 'Service Desk Monthly Report'[Queue Call] )
Then adjust the first measure as follows
AVG =
DIVIDE (
[Total Calls],
CALCULATE ( [Total Calls], ALL ( 'Service Desk Monthly Report'[Agent] ) )
)
@Anonymous
Seems you have other columns in the visual. Please try
AVG =
DIVIDE (
[Total Calls],
CALCULATE ( [Total Calls], ALL ( 'Service Desk Monthly Report' ) )
)
Seems we getting closer 🙂
However, the average for first row should be 0.108333 and not 0.06
@Anonymous
Please paste the name of all the columns involved in this table visual.
@Anonymous
Please try
AVG =
DIVIDE (
[Total Calls],
CALCULATE (
[Total Calls],
ALL ( StafFList[Agent], 'Service Desk Monthly Report'[Queue Call] )
)
)
Hi @Anonymous
First you need to create a measure to get the total number of all agent called
TotalAgentCalls = SUM('Table'[Direct]) + SUM('Table'[Recover])
Then create a second measure
Average = DIVIDE(SUM('Table'[Queue]),'Table'[TotalAgentCalls])
This should give you the result you are looking for
Sorry @Seanan , I just want to calculate it on 'Queue Call' only.
I tried as you suggestedby creating 2 different measure but still it's not giving the correct result 😞
Hi @Anonymous
Could you try this:
Create 1st measure
Avg = AVERAGE(Queue[QueueCalls])
Create 2nd measure
TotalAverage = CALCULATE([Avg],ALL(Queue[QueueCalls]))
Create final measure (to be used in your table)
FinalAvg = DIVIDE('Queue'[Avg],'Queue'[TotalAverage])
Sorry it's still giving me 1.00 as value for all of them.
I've attached sample data if you want to play with it.
Agent | Queue Call |
Alex | 312 |
Alvis | 30 |
Andrew | 26 |
Annaliese | 0 |
Anton | 71 |
Chris | 53 |
David | 36 |
Hi @Anonymous
Strange it seems to be working fine on my test report. Would you be able to send the PBIX file containing only relevent information and non sensitive data?
Please advise how I can send you the pbix file.
@Anonymous You can save a copy of the file, remove any sensitive and irrelevent data and then send it in dropbox if that is easiest for you?
Try this link, it's fromk my onedrive:
Hi @Anonymous
Please find attached the dropbox link with the PBIX file.
The SubAvg column is the final result that you are looking for.
https://www.dropbox.com/s/a52hlwjy7nhqu8w/SD%20Monthly%20Reporting.pbix?dl=0
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |