Total Calls for repeated telephone numbers

[Caseyc1983]

Hello,

I am looking to find a calculation that will give me a value for the total number of calls per day, week, month, from telephone numbers which have contacted us on more than one occassion.  My table list all calls by date, telephone number, and a unique call reference number.   Total should exclude calls from customers who have only contacted us once during the timeframe specified.  Any help would be much appreciated.

ID	Date	Telephone Number
1	04/11/2019	12345
2	04/11/2019	13246
3	04/11/2019	12222
4	04/11/2019	12345
5	04/11/2019	12345

[Anonymous · Accepted solution]

Hi @Caseyc1983 , 

the first question can be covered with the measure like: 

M2 = 
var ValidCalls = Filter(Calls,Not Calls[callerid] IN {"Anonymous", "Withheld", "Private"})
var tablevar = 
 SUMMARIZE(
 ValidCalls, 
 Calls[callerid],
 "TC", sumx(Calls, if (COUNTROWS('Calls')>1,1,0)
 ) )
return
SUMX(tablevar, [TC])

The first parameter of Summarize exepts a table. If you change the ValidCalls varible you can control the table that is used by the summarize statement. 

DistinctCustomers per period is basically an additional wrapper around the summarize command:

M3 = 
var ValidCalls = Filter(Calls,Not Calls[callerid] IN {"Anonymous", "Withheld", "Private"})
var NumberOfDistinctCustomers = 
COUNTROWS (
filter (
    ADDCOLUMNS (
        SUMMARIZE (
     ValidCalls, 
     Calls[callerid]), 
 "TC", CALCULATE(sumx(Calls, if (COUNTROWS('Calls')>1,1,0)))
    ) ,
    [TC]>0)
    )
return
NumberOfDistinctCustomers

 Give it a try

[Caseyc1983]

Sorry to be a pain but how would I add 'is not blank' to those filters as well.

thanks 

[Caseyc1983]

Just ignore that, figured it out.  Just being daft!

[Caseyc1983]

Thank you so much guys, you're help is much appreciated!!!!

[Anonymous]

Hi @Caseyc1983 , 

This measure works for me, but only in a matrix, where the timeframe is still part of the evaluation context.

My Measure:

M1 :=

var tablevar =  SUMMARIZE(

Calls,

Calls[callerid]

, "TC", sumx(Calls, if (COUNTROWS('Calls')>1,1,0)

)

)

return

SUMX(tablevar, [TC])

Hope this helps, if not then I am also interested in the final solution 🙂

regards,

Jan

[Caseyc1983]

Thank you so much for this, worked a treat.  Is there anyway of applying some form of filter to this calculation in order to exclude any calls from numbers which are witheld or anonymous?   Likewise, do you have a solution which counts the distinct number of customers who have called more than once on a given day, week or month?  This would also need to have the ability to filter / exclude certain things such as witheld numbers

Really appreciate you getting back to me!!