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

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
1 year ago

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

AllisonB's avatar
AllisonB
Frequent Visitor
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.