Forum Discussion

Jingspat's avatar
Jingspat
Frequent Visitor
9 years ago

Sum with filtering on multiple columns

I want to be able to get a total for Call Time if the name is in either Call From or Call to.  The table looks like this:

 

 

Call DateCall FromCall ToCall TypeCall Time (seconds)Talk Time (seconds)
6/15/2017 11:23 AMName 1 <312>1234567891outgoing100
6/15/2017 11:23 AM9876543211Name 1 <312>incoming1411
6/15/2017 11:23 AMName 2 <319>Name3 <332>outgoing5642

 

How do I create that measure?

 

4 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi Jingspat,

     

    If I understand you correctly, you should be able to use the formula below to create a new measure to calculate the total call time in your scenario. :smileyhappy:

    Total Call Time = 
CALCULATE (
    SUM ( Table1[Call Time (seconds)] ),
    FILTER (
        Table1,
        FIND ( "Name", Table1[Call From],, 0 ) > 0
            || FIND ( "Name", Table1[Call To],, 0 ) > 0
    )
)

     

    Regards

    • Jingspat's avatar
      Jingspat
      Frequent Visitor
      9 years ago

      Thanks so much for the response v-ljerr-msft!!!  We have hundreds of varying names, if I understand your propopsed solution, that would just search by one name.  Is it possible to have it show the total talk time for all names?

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi Jingspat,

         

        Could you try the formula below to see if it works? :smileyhappy:

        Total Call Time =
CALCULATE (
    SUM ( Table1[Call Time (seconds)] ),
    FILTER (
        Table1,
        FIND ( "<", Table1[Call From],, 0 ) > 0
            || FIND ( "<", Table1[Call To],, 0 ) > 0
    )
)

         

        Regards