Forum Discussion

AllisonB's avatar
AllisonB
Frequent Visitor
1 year ago
Solved

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...
Need Help
  • AllisonB's avatar
    AllisonB
    1 year ago

    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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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])

 

AllisonB's avatar
AllisonB
Frequent Visitor
1 year ago

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