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

 

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

     

5 Replies

  • amitchandak's avatar
    amitchandak
    Super 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])

         

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

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