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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
smather
Helper III
Helper III

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 Disposition Name] = "Handled",

SWITCH(TRUE(),

('Call'[Queue Time]) <= TIME(0,0,10),"Handled Within 10 Seconds",
('Call'[Queue Time]) <= TIME(0,0,20), "Handled Within 20 Seconds",
('Call'[Queue Time]) <= TIME(0,0,30), "Handled Within 30 Seconds",
('Call'[Queue Time]) < TIME(0,0,60), "Handled Outside 30 Seconds",
('Call'[Queue Time]) >= TIME(0,0,60), "Handled Outside 60 Seconds"))

 

Now, when I select it to show it as a percentage of the grand total, it's obviously doing a grand total per catagory - what I need is for 'Handled Within 10 Seconds" and "Handled Within 20 Seconds" to add up -- because if it's answered within 20, it's been answered within 10, but I cannot for the life of me figure out how to do that. 

 

So, for example: 

 

10 Seconds - 80%

20 Seconds - 5% + 80%(from 10 seconds) = 85%

30 Seconds - 3% + 85% = 87%

 

And so on and so forth.

 

Thank you!

1 ACCEPTED SOLUTION

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 )
)
)
)
)

yingyinr_0-1638433871038.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-yiruan-msft
Community Support
Community Support

Hi @smather ,

I'm sorry that I still don't understand what's your expected result... I have created a sample table based on your description and the result you may want, please help me to confirm if it is what you want. If not, please share some sample data from your table (excluding sensitive data) and your expected result. If there is a calculation involved, please also provide the logic for the calculation. Thank you.

Sample data:

yingyinr_0-1637912766184.png

Expected result: wheteher the field Percent in below table is your expected result?

yingyinr_2-1637912962571.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft @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 NameQueue TimeHandled and AbandonedX
Handled00:00:05Handled Within 10 SecondsH1
Handled00:00:32Handled in SLT (60 Seconds)H4
Abandoned00:00:15AbandonedA1
Handled 00:00:03Handled Within 10 SecondsH1

 

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. 

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 )
)
)
)
)

yingyinr_0-1638433871038.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@v-yiruan-msft 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) ? 

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
)

yingyinr_0-1638496715370.png

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-yiruan-msft  - 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?

Hi @v-yiruan-msft 

 

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?

Thank you @v-yiruan-msft ! That's perfect, I really appreciate your help. 😊

Greg_Deckler
Super User
Super User

@smather Without sample data tough to be specific but I would just figure out what category I am in and then add the two together (10 seconds and 20 seconds). 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler Do you know how I'd add these figures up? 

Hi @Greg_Deckler 

 

My data looks a little like this: 

 

Session IDStart TimeEnd TimeQueue TimeTalk TimeSectorHandledHandled/AbandonedNumber CalledCountable Call

 

There's a lot more cols than that, but it's just a general idea. The handled col has the code in it above, so if it's below ten seconds, that row will say "Handled Within 10 seconds", if it's below twenty seconds, "Handled Within 20 Seconds", etc. It's not a number I can physically add up. It adds up the countable calls and sorts them into "Handled Within 10/20/30/Outside 60 Seconds" and when you do show as a percentage of the grand total, it does - but I need thefigures to roll up, so whatever is in 10 seconds adds onto 20 seconds numbers. 

 

I don't know how to add them up, that's what I'm asking, poorly. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors