Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KAJAR
New Member

SUM total number of calls per phone number above 2 calls

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 😄
1 ACCEPTED SOLUTION

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

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @KAJAR 
Please try

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

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

johnt75
Super User
Super User

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

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? 

KAJAR_1-1682420743570.png

 

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

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

Thank you so much - It's working perfect!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors