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
Anonymous
Not applicable

How to calculate average of simple column

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.

 

My column is not calculating the Average properlyMy column is not calculating the Average properly

 

Assuming the total number of calls is 2880, the result I want is:

hitman2022_0-1656069195656.png

 

1 ACCEPTED 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

View solution in original post

30 REPLIES 30
tamerj1
Super User
Super User

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
Not applicable

hitman2022_0-1656067785111.pnghitman2022_1-1656067801108.png

 

@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
Not applicable

hitman2022_0-1656070843088.png

 

@Anonymous 
Seems you have other columns in the visual. Please try

AVG =
DIVIDE (
    [Total Calls],
    CALCULATE ( [Total Calls], ALL ( 'Service Desk Monthly Report' ) )
)
Anonymous
Not applicable

Seems we getting closer 🙂
However, the average for first row should be 0.108333 and not 0.06 

hitman2022_0-1656071984265.png

 

hitman2022_1-1656072043380.png

 

@Anonymous 
Please paste the name of all the columns involved in this table visual.

Anonymous
Not applicable

 

hitman2022_0-1656072729532.png

 

Anonymous
Not applicable

hitman2022_1-1656072781134.png

 

@Anonymous 

Please try

AVG =
DIVIDE (
    [Total Calls],
    CALCULATE (
        [Total Calls],
        ALL ( StafFList[Agent], 'Service Desk Monthly Report'[Queue Call] )
    )
)
Anonymous
Not applicable

Seanan
Solution Supplier
Solution Supplier

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

PBIDesktop_OOCQgjf638.png

Anonymous
Not applicable

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 😞

 

hitman2022_2-1656068720980.png

 

hitman2022_3-1656068772828.png

 

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])
Anonymous
Not applicable

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.

AgentQueue Call
Alex312
Alvis30
Andrew26
Annaliese0
Anton71
Chris53
David36

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?

Anonymous
Not applicable

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?

Anonymous
Not applicable

Try this link, it's fromk my onedrive:

https://1drv.ms/u/s!AnEZNSVeMtRbjnKv0LcWjjbFUfoy?e=z3eu0m

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

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.