Forum Discussion

smather's avatar
smather
Icon for Helper III rankHelper 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

smather's avatar
smather
Icon for Helper III rankHelper 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
    Icon for Helper III rankHelper 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
    Icon for Helper III rankHelper 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?