Forum Discussion

smather's avatar
smather
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's avatar
    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 (
    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 (
    _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

Anonymous's avatar
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 (
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 (
_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's avatar
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's avatar
    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's avatar
      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 10558
      Handled Within 20705
      Handled Within 30776
      Handled in 60842
      Outside SLT150

       

      % of calls measure gives me this:

       

      Abandoned 7.61%
      Handled Within 1047.71%
      Handled Within 2060.28%
      Handled Within 3066.35%
      Handled in 6072.00%
      Outside SLT12.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 1051.62%
      Handled Within 2065.22%
      Handled Within 3071.79%
      Handled in 6077.89%
      Outside SLT13.88

       

      8.23%, 51.62%, 65.22%, 71.79%, 77.89%, 13.88%

       

      is it because I have my data filtered?

    • smather's avatar
      smather
      Helper III
      4 years ago

      Hi Anonymous  - apologies, me again. This is all perfect until it comes to filtering via month. Is there something I need to add to the VAR so it regonises being filtered via month and to still do the cumulative figures?