cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Creating a ratio based on a calculated measure

Hi,

 

I'm trying to calculate a first contract resolution rate for our customer service department. My data source is Salesforce where I use the Case object and Case History object for this calculation. So far I've managed to calculate the number of redirections per case, by looking at the Case History object and counting the number of owners each CaseID has had. We've set the threshold to 3, meaning that 1 and 2 case owners are considered resolved at First Contact, 3 or more are not considered resolved at First Contact. Now let's say we have 4 cases, whereof 3 only needed 2 case owners to be resolved and 1 needed 4. We would then have a 75% First Contact Resolution rate.

 

I manage to calculate the number of redirections per case (as a measure) and even get to a boolean (also a measure) for each case number, but from here I'm not able to get to the ratio. Any help I can get is much appreciated.

 

 

DAX:

 

# of case redirections = CALCULATE(COUNTAX(SF_Kundcasehistorik;SF_Kundcasehistorik[CaseId]);or(SF_Kundcasehistorik[Field]="Owner";SF_Kundcasehistorik[Field]="ownerassignment"))

 

FCR = if([# of case redirections]<3;1;0)

 

 

Kind regards,

Pedro

9 REPLIES 9
themistoklis New Contributor
New Contributor

Re: Creating a ratio based on a calculated measure

@Anonymous

 

How about this formula:

 

FCR = IF([# of case redirections]<3;[# of case redirections];0) / [# of case redirections]
Anonymous
Not applicable

Re: Creating a ratio based on a calculated measure

Many thanks themistoklis, but it doesn't seem to do the trick. Just as my FCR calculation, the row by row calculation is correct, but I can't get to an average for all cases. In the visual it does not allow me to do any calculation or slicing on the FCR. Anymore suggestions? Thx. 

themistoklis New Contributor
New Contributor

Re: Creating a ratio based on a calculated measure

Can you share the workspace if possible and an image on how you want the data to look like?

 

Also you cant put measure as slicers

Anonymous
Not applicable

Re: Creating a ratio based on a calculated measure

I can't share the data. I'm trying to paste a snip-out of the canvas and visuals, but nothing happens when I paste it in here. 

 

I would like to present a FCR ratio to the Customer Service team each month. Ideally I would display the ratio as a KPI visual against a target ratio. Right now the measure gives me this on a row level, saying it's either 100 or 0%, but I would need the average for all rows (cases).

themistoklis New Contributor
New Contributor

Re: Creating a ratio based on a calculated measure

@Anonymous

 

You should click on photos button first and then add the images

Anonymous
Not applicable

Re: Creating a ratio based on a calculated measure

FCR snipout.PNG

 

As you can see, the individual rows are correct, but the total is 0. It doesn't let me to make an average of FCR for all rows.

themistoklis New Contributor
New Contributor

Re: Creating a ratio based on a calculated measure

@Anonymous

 

Thanks for the photos

Can you also send the formulas that you have for FCR and FCR%

Gravanita Regular Visitor
Regular Visitor

Re: Creating a ratio based on a calculated measure

@Anonymous 
See if this helps:

Capture2.JPG

Example file can be found Here

themistoklis New Contributor
New Contributor

Re: Creating a ratio based on a calculated measure

@Anonymous

 

You can also try these formulas

 

# of case redirections =

SUMX ( VALUES ( SF_Kundcasehistorik[CaseNumber]);

CALCULATE(COUNTAX(SF_Kundcasehistorik;SF_Kundcasehistorik[CaseId]);or(SF_Kundcasehistorik[Field]="Owner";SF_Kundcasehistorik[Field]="ownerassignment"))
)

 

FCR = if( IFERROR([# of case redirections],0)<3 ;1 ;0)

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)