Forum Discussion

Helper III

4 years ago

Solved

Call Stats - Roll Up Figures

Hi 🙂 I have some call stat data I'm struggling with (my brain is refusing to work) and any help would be fab. I have a col that sorts our calls like so: Handled = IF('Call'[Contact Di...

Need Help

Anonymous

4 years ago

Hi smather ,

You can update the formula of your measure [TestMeasure] as below and check whether you can get the correct result.

TestMeasure =

VAR _selhandabdon =

SELECTEDVALUE ( 'Call'[Handled and Abandoned] )

VAR TenSecs =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

ALLSELECTED ( 'Call' ),

'Call'[Handled and Abandoned] = "Handled Within 10 Seconds"

'Call'[X] <= MAX ( 'Call'[X] )

)

VAR TwentySecs =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

ALLSELECTED ( 'Call' ),

'Call'[Handled and Abandoned] = "Handled Within 20 Seconds"

'Call'[X] <= MAX ( 'Call'[X] )

)

VAR ThirtySecs =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

ALLSELECTED ( 'Call' ),

'Call'[Handled and Abandoned] = "Handled Within 30 Seconds"

'Call'[X] <= MAX ( 'Call'[X] )

)

VAR SixtySecs =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

ALLSELECTED ( 'Call' ),

'Call'[Handled and Abandoned] = "Handled in SLT (60 Seconds)"

'Call'[X] <= MAX ( 'Call'[X] )

)

VAR Abandoned =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

FILTER ( ALLSELECTED ( 'Call' ), 'Call'[Handled and Abandoned] = "Abandoned" ),

'Call'[X] <= MAX ( 'Call'[X] )

)

RETURN

IF (

_selhandabdon = "Handled Within 10 Seconds",

TenSecs,

IF (

_selhandabdon = "Handled Within 20 Seconds",

TenSecs + TwentySecs,

IF (

_selhandabdon = "Handled Within 30 Seconds",

TenSecs + TwentySecs + ThirtySecs,

IF (

_selhandabdon = "Handled in SLT (60 Seconds)",

TenSecs + TwentySecs + ThirtySecs + SixtySecs,

IF ( _selhandabdon = "Abandoned", Abandoned )

)

Best Regards

smather

Helper III

4 years ago

Hi Anonymous Greg_Deckler

Sorry, I'm not the best at explaining and I've been trying to figure it out on my own. Let me give you an example of data I'm working with:

Contact Disposition Name	Queue Time	Handled and Abandoned	X
Handled	00:00:05	Handled Within 10 Seconds	H1
Handled	00:00:32	Handled in SLT (60 Seconds)	H4
Abandoned	00:00:15	Abandoned	A1
Handled	00:00:03	Handled Within 10 Seconds	H1

X is a formula:

X = SWITCH(
    'Call'[Handled and Abandoned],
    "Handled Within 10 Seconds", "H1",
    "Handled Within 20 Seconds", "H2",
    "Handled Within 30 Seconds", "H3",
    "Handled in SLT (60 Seconds)", "H4",
    "Abandoned", "A1",
    "U1"
)

I've tried to do a VAR fomula to get what I want, but I'm being told MAXX doesn't work with boolean so I'm a bit stuck again:

TestMeasure = 

VAR TenSecs = CALCULATE(SUM('Call'[All Calls]),FILTER(FILTER(ALLSELECTED('Call'), 'Call'[Handled and Abandoned] = "Handled Within 10 Seconds"),'Call'[X] <= MAX('Call'[X])))
VAR TwentySecs = CALCULATE(SUM('Call'[All Calls]),FILTER(FILTER(ALLSELECTED('Call'), 'Call'[Handled and Abandoned] = "Handled Within 20 Seconds"),'Call'[X] <= MAX('Call'[X])))
VAR ThirtySecs = CALCULATE(SUM('Call'[All Calls]),FILTER(FILTER(ALLSELECTED('Call'), 'Call'[Handled and Abandoned] = "Handled Within 30 Seconds"),'Call'[X] <= MAX('Call'[X])))
VAR SixtySecs = CALCULATE(SUM('Call'[All Calls]),FILTER(FILTER(ALLSELECTED('Call'), 'Call'[Handled and Abandoned] = "Handled in SLT (60 Seconds)"),'Call'[X] <= MAX('Call'[X])))
VAR Abandoned = CALCULATE(SUM('Call'[All Calls]),FILTER(FILTER(ALLSELECTED('Call'), 'Call'[Handled and Abandoned] = "Abandoned"),'Call'[X] <= MAX('Call'[X])))


RETURN IF(MAXX('Call','Call'[Handled and Abandoned] = "Handled Within 10 Seconds"),TenSecs,IF(MAXX('Call','Call'[Handled and Abandoned] = "Handled Within 20 Seconds"),TenSecs+TwentySecs,IF(MAXX('Call','Call'[Handled and Abandoned] = "Handled Within 30 Seconds"),TenSecs+TwentySecs+ThirtySecs,IF(MAXX('Call','Call'[Handled and Abandoned] = "Handled in SLT (60 Seconds)"),TenSecs+TwentySecs+ThirtySecs+SixtySecs,IF(MAXX('Call','Call'[Handled and Abandoned] = "Abandoned"),Abandoned)))))

Hopefully TestMeasure gives more of an idea of what I'm after - to add up 10 & 20 second calls together and so on - if it's handled in 10, it's handled within 20, 30 and 60.

When I get that working, I'll then be able to tweak it to get the %s.

Any help would be great.

Anonymous

Not applicable

4 years ago

Hi smather ,

You can update the formula of your measure [TestMeasure] as below and check whether you can get the correct result.

TestMeasure =

VAR _selhandabdon =

SELECTEDVALUE ( 'Call'[Handled and Abandoned] )

VAR TenSecs =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

ALLSELECTED ( 'Call' ),

'Call'[Handled and Abandoned] = "Handled Within 10 Seconds"

'Call'[X] <= MAX ( 'Call'[X] )

)

VAR TwentySecs =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

ALLSELECTED ( 'Call' ),

'Call'[Handled and Abandoned] = "Handled Within 20 Seconds"

'Call'[X] <= MAX ( 'Call'[X] )

)

VAR ThirtySecs =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

ALLSELECTED ( 'Call' ),

'Call'[Handled and Abandoned] = "Handled Within 30 Seconds"

'Call'[X] <= MAX ( 'Call'[X] )

)

VAR SixtySecs =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

ALLSELECTED ( 'Call' ),

'Call'[Handled and Abandoned] = "Handled in SLT (60 Seconds)"

'Call'[X] <= MAX ( 'Call'[X] )

)

VAR Abandoned =

CALCULATE (

SUM ( 'Call'[All Calls] ),

FILTER (

FILTER ( ALLSELECTED ( 'Call' ), 'Call'[Handled and Abandoned] = "Abandoned" ),

'Call'[X] <= MAX ( 'Call'[X] )

)

RETURN

IF (

_selhandabdon = "Handled Within 10 Seconds",

TenSecs,

IF (

_selhandabdon = "Handled Within 20 Seconds",

TenSecs + TwentySecs,

IF (

_selhandabdon = "Handled Within 30 Seconds",

TenSecs + TwentySecs + ThirtySecs,

IF (

_selhandabdon = "Handled in SLT (60 Seconds)",

TenSecs + TwentySecs + ThirtySecs + SixtySecs,

IF ( _selhandabdon = "Abandoned", Abandoned )

)

Best Regards

smather
Helper III
4 years ago
Thank you Anonymous ! That's perfect, I really appreciate your help. 😊
smather
Helper III
4 years ago
Anonymous sorry to be a nightmare, would you have any idea how to tweak it to make another measure to get the % of calls from all calls in each cat (Handled With 10/20/30 etc) ?
- Anonymous
  Not applicable
  4 years ago
  Hi smather ，
  
  You can create a measure as below to get the % of calls from all calls in each category, later please check whether it can return the correct result....
  
  % of calls = DIVIDE ( [TestMeasure], CALCULATE ( SUM ( 'Call'[All Calls] ), ALL ( 'Call' ) ), 0 )
  
  If the above one is not what you want, please provide your expected result with the calculation logic for the % of the calls from all calls and special examples. Thank you.
  
  Best Regards
  - smather
    Helper III
    4 years ago
    Hi Anonymous
    
    Thank you for that! This is the result I'm getting for Jan:
    
    Total incoming calls: 1081 - broken out into, as per TestMeasure, which is correct:
    
    Abandoned 89
    Handled Within 10 558
    Handled Within 20 705
    Handled Within 30 776
    Handled in 60 842
    Outside SLT 150
    
    % of calls measure gives me this:
    
    Abandoned 7.61%
    Handled Within 10 47.71%
    Handled Within 20 60.28%
    Handled Within 30 66.35%
    Handled in 60 72.00%
    Outside SLT 12.83%
    
    it's messing up the format: 7.61%, 47.71%, 60.28%, 66.35%, 72.00% & 12.83% in order.
    
    I was expecting from Jan, figures more like this:
    
    Abandoned 8.23%
    Handled Within 10 51.62%
    Handled Within 20 65.22%
    Handled Within 30 71.79%
    Handled in 60 77.89%
    Outside SLT 13.88
    
    8.23%, 51.62%, 65.22%, 71.79%, 77.89%, 13.88%
    
    is it because I have my data filtered?

Forum Discussion

Call Stats - Roll Up Figures

Related content

FIFA WORLD CUP 2026: Stats Analysis Hub

Calculating active figures

referer (referring) URL in usage stats?

Service Level for Call Stats

Need help calculating figures in a column to weekly figures

Abandoned	89
Handled Within 10	558
Handled Within 20	705
Handled Within 30	776
Handled in 60	842
Outside SLT	150

Abandoned	7.61%
Handled Within 10	47.71%
Handled Within 20	60.28%
Handled Within 30	66.35%
Handled in 60	72.00%
Outside SLT	12.83%

Abandoned	8.23%
Handled Within 10	51.62%
Handled Within 20	65.22%
Handled Within 30	71.79%
Handled in 60	77.89%
Outside SLT	13.88