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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
AllisonB
Frequent Visitor

Creating a calculation to add data together based on data in other columns

Hello hoping someone can help with my query

I have a set of telephone data as below and I need to add together the talk times and wrap times (as seperate figures) where the same phone number appears on the same day, thus giving a total amount of the for each customer on each day. I also need to factor in the queue and be able to do a calculation based on each queue name, so it would be per customer, per day on an individual queue.  I thought about adding a custom column with an if calculation but then it will add a total on each line the phone number appears and then it would make the calculations for the visuals difficult to create.

Would creating a new table for the data be the best way to go? Maybe adding an index column and then creating variables to add together the calls from the same day?

 

for the output/visual I need to calculate the average talk time and wrap time per customer, per day, per queue.

For example:

01234 567890 had 3 calls on one day, one 1234 minutes on outbound 1, one 2345 minutes on outbound 2 and one 345 minutes long on outbound 1, so the total amount of time would for this customer on that day would be outbound 1, 1579, and for outbound 2, 2345. 

02345 678901 had 1 call on the same day lasting 3456 minutes on outbound 1

Add together the talk times and divide by the number of customers per queue:

outbound 1= 1579+3456=5035/2=2517.5

outbound 2 = 2345/1=2345

So the average call length per customer for that day would be 2517.5 for outbound 1 and 2345 for outbound 2

 

Any help on how best to do this would be greatfully received

 

Ignore the average in the column header for wrap, it is the total time for each call not the average in that column

AllisonB_0-1753198581351.png

 

1 ACCEPTED SOLUTION

Thanks for this but unfortunately didnt quite work for how I need the visuals, it has been really useful though as I used the basis to create a table and the created measures from that table

 

Table

Total times = SUMMARIZE('Outbound Calls','Outbound Calls'[Date],'Outbound Calls'[Customer Phone],'Outbound Calls'[Queue],'Outbound Calls'[Agent],"Total talk",sum('Outbound Calls'[Outbound Talk Time]),"Total wrap",SUM('Outbound Calls'[Outbound Wrap Up Time]))
 
Measure
Average Talk Time Outbound 1 =
VAR _HandledConversations = CALCULATE(COUNT('Total times'[Customer Phone]),'Total times'[Queue]="Outbound 1")
VAR _TotalTalkTime = CALCULATE(SUM('Total times'[Total talk]),'Total times'[Queue]="Outbound 1")
VAR _Seconds = IF(_HandledConversations <> 0, _TotalTalkTime / _HandledConversations, 0)
VAR _Minutes = INT(DIVIDE(_Seconds, 60))
VAR _RemainingSeconds = MOD(_Seconds, 60)
VAR _Hours = INT(DIVIDE(_Minutes, 60))
VAR _RemainingMinutes = MOD(_Minutes, 60)

RETURN
    FORMAT(_Hours, "00") & ":" &
    FORMAT(_RemainingMinutes, "00") & ":" &
    FORMAT(_RemainingSeconds, "00")
 
Visual
AllisonB_0-1753267661573.png

 

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

what is your definition of "Day"?  Single timezone?  What should happen to calls that cross past "midnight"?

amitchandak
Super User
Super User

@AllisonB , better to measures 
talk time Measure= Sum([Talk Time]) 

Avg per customer = Sumx(Summarize(Table, Table[Customer]), [talk time Measure]) 

 

Avg per customer per day= Sumx(Summarize(Table, Table[Customer], Table[Date]), [talk time Measure]) 

 

 

Avg per customer per day per Q= Sumx(Summarize(Table, Table[Customer], Table[Date], Table[Queue]), [talk time Measure]) 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Thanks for this but unfortunately didnt quite work for how I need the visuals, it has been really useful though as I used the basis to create a table and the created measures from that table

 

Table

Total times = SUMMARIZE('Outbound Calls','Outbound Calls'[Date],'Outbound Calls'[Customer Phone],'Outbound Calls'[Queue],'Outbound Calls'[Agent],"Total talk",sum('Outbound Calls'[Outbound Talk Time]),"Total wrap",SUM('Outbound Calls'[Outbound Wrap Up Time]))
 
Measure
Average Talk Time Outbound 1 =
VAR _HandledConversations = CALCULATE(COUNT('Total times'[Customer Phone]),'Total times'[Queue]="Outbound 1")
VAR _TotalTalkTime = CALCULATE(SUM('Total times'[Total talk]),'Total times'[Queue]="Outbound 1")
VAR _Seconds = IF(_HandledConversations <> 0, _TotalTalkTime / _HandledConversations, 0)
VAR _Minutes = INT(DIVIDE(_Seconds, 60))
VAR _RemainingSeconds = MOD(_Seconds, 60)
VAR _Hours = INT(DIVIDE(_Minutes, 60))
VAR _RemainingMinutes = MOD(_Minutes, 60)

RETURN
    FORMAT(_Hours, "00") & ":" &
    FORMAT(_RemainingMinutes, "00") & ":" &
    FORMAT(_RemainingSeconds, "00")
 
Visual
AllisonB_0-1753267661573.png

 

Hi @AllisonB,

Kindly try the DAX calculations provided below. If the issue still persists, please share the sample data in a workable format (such as text or an Excel file, rather than screenshots), along with the expected output. This will help us assist you more effectively.

 

 

Create an aggregated table 
CustomerDailySummary = SUMMARIZE('Outbound Calls',
    'Outbound Calls'[Date],'Outbound Calls'[Customer Phone],'Outbound Calls'[Queue],
    "TotalTalkTime", SUM('Outbound Calls'[Total Outbound Talk Time]),
    "TotalWrapTime", SUM('Outbound Calls'[Average Outbound Wrap Up Time]))

Measure:
AvgTalkTimePerCustomer =
AVERAGEX(FILTER('CustomerDailySummary', 'CustomerDailySummary'[Queue] = "Outbound 1"),
    [TotalTalkTime])
Measure:
AvgWrapTimePerCustomer = AVERAGEX(FILTER('CustomerDailySummary', 'CustomerDailySummary'[Queue] = "Outbound 1"),[TotalWrapTime])

 

I've already solved it, please see my reply above, I have now marked it as solved to stop any future confusion. 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

Top Solution Authors