topic Re: SUM total number of calls per phone number above 2 calls in DAX Commands and Tips

SUM total number of calls per phone number above 2 calls

KAJAR — Tue, 25 Apr 2023 09:39:13 GMT

Hi there!

I keep running my head against the wall with this one and would love any kind of help 😄

Goal:

A single measure I can put on a "Card" in Power BI that shows the total number of calls for all phone numbers that have called above 2 times.

Source = phone number
CallAnswered = 1's (if a number have called there is a new row inserted)

I've tried this:

KJ - SUM kald source = CALCULATE(

SUM(CallEvents[CallAnswered]),

FILTER(

SUMMARIZE(

CallEvents,

CallEvents[source],

"Total Calls", COUNT(CallEvents[CallAnswered])

[Total Calls] >= 3

ALL(CallEvents)

)

But it still includes numbers that have less than 3 calls in total.

I've also tried this:

KJ - SUM kald source = CALCULATE(

SUM(CallEvents[CallAnswered]),

FILTER(

SUMMARIZE(

CallEvents,

CallEvents[source],

"Total Calls", COUNT(CallEvents[CallAnswered])

[Total Calls] >= 3 && [Total Calls]<3

ALL(CallEvents)

)

But this excludes all phone numbers both the numbers that have recieved over and under 2 calls in total.

What am I missing here?

I appriciate any help 😄

Re: SUM total number of calls per phone number above 2 calls

johnt75 — Tue, 25 Apr 2023 10:54:49 GMT

Try

Total calls with more than 2 calls = VAR SummaryTable = ADDCOLUMNS ( VALUES ( 'CallEvents'[source] ), "@num calls", CALCULATE ( COUNTROWS ( 'CallEvents' ) ) ) VAR Result = SUMX ( FILTER ( SummaryTable, [@num calls] >= 3 ), [@num calls] ) RETURN Result

Re: SUM total number of calls per phone number above 2 calls

tamerj1 — Tue, 25 Apr 2023 11:05:48 GMT

Hi KAJAR
Please try

KJ - SUM kald source = SUMX ( FILTER ( SUMMARIZE ( CallEvents, CallEvents[source], "Total Calls", SUM ( CallEvents[CallAnswered] ) ), [Total Calls] >= 3 ), CallEvents[CallAnswered] )

Re: SUM total number of calls per phone number above 2 calls

KAJAR — Tue, 25 Apr 2023 11:08:11 GMT

Hi johnt75! Thanks for your reply.

It no longer includes below 2 calls, but now it has 400.000 more calls as total than it should. Did I do something wrong?

The two totals marked with yellow is the actual total (depending on if calculated over/under a cetain time limit)

Re: SUM total number of calls per phone number above 2 calls

johnt75 — Tue, 25 Apr 2023 11:15:45 GMT

I hadn't included a filter to check that CallAnswered is 1.

Total calls with more than 2 calls = VAR SummaryTable = ADDCOLUMNS ( VALUES ( 'CallEvents'[source] ), "@num calls", CALCULATE ( COUNTROWS ( 'CallEvents' ), 'CallEvents'[Call Answered] = 1 ) ) VAR Result = SUMX ( FILTER ( SummaryTable, [@num calls] >= 3 ), [@num calls] ) RETURN Result

Re: SUM total number of calls per phone number above 2 calls

KAJAR — Tue, 25 Apr 2023 11:24:26 GMT

Thank you so much - It's working perfect!

Re: SUM total number of calls per phone number above 2 calls

KAJAR — Tue, 25 Apr 2023 11:33:07 GMT

Thanks for the reply tamerj1.
I saw it after johnt75 solved it, but thank you so much!