FIRST CALL RESOLUTION %

[Trojden]

Hi,

I wan to create a measure for obtaining FCR from my call log.

In the call log I have it has unique callkey, not uniqe: casekay and statuskey paired with that call and indicator of direction (Outgoing/Ingoing).
I need to show: count of callkeys with specific status key, count of statuses = 1 for a distinct case, and direction = "Outgoing".

How could I do that?
I have separate dim for statuses, call table is my fact table. Cases are within the call table for now.

[amitchandak]

@Trojden , data you share is not sufficient to suggest formula.

Can you share sample data and sample output in table format?

[Trojden]

@amitchandak  My sql querries which are working:

select count(CaseKey) as FCRCOUNT from CALLS
where CaseKey IN
(
	select CaseKey from POWER_BI_AGENTS_OUTGOING_CALLS group by CaseKey
	HAVING COUNT(WorkflowStateKey) < 2)
AND WorkflowStateKey = '2313E516-3568-462B-A61A-E984199DD093' AND Direction = 'Outgoing')

select count(*) as TotalCount from CALLS

FCR % would be a division of FCRCOUNT by TotalCount

  Sample data:

CallKey CaseKey WorkflowStateKey Direction Connect
BC6C7FB8-A36C-CB73-AC34-00E62BF264E7 44E2CDD8-A6D1-4CB7-805D-2D83487C90EF 514E5977-7DE8-48D4-AF00-6A7AA795EAB1 outgoing 0
3A9AF9E4-7961-E248-142D-00E76B872F33 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 1
7C85C5BC-E280-E978-4883-00EC66BE313D 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 1
B0152FFA-6A1D-F236-9B3D-00EF870BA2EC 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 incoming 1
651B9477-D12F-1E1D-8144-00F13F2B0FB0 B2825F29-6769-4D25-BBC2-042E500AB115 1B9DD1AE-CAF4-4012-A6EE-0E385F6FA2E6 outgoing 1
E472B200-868F-1AC4-9909-00F74BD59B40 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 incoming 1
F1E37D16-1DFE-DC5E-FB79-00F863B0A845 CDB1CC8C-A907-4539-B8DB-C01243893271 CC95D4B4-0076-474F-81E8-C7591A366BB2 outgoing 1
C5F45AE0-7002-4116-8494-00F897C0C342 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 0
F6730D3C-523E-B840-ACD9-00FC90EF58DF 8D3B68B1-470B-48D0-B47C-B445B1C2B0D5 00000000-0000-0000-0000-000000000000 outgoing 0
2CF5E853-C9E7-6198-6A28-01004D555C09 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 0
EBDDE58B-1B89-19DE-8CA4-0101A13B75A5 23423813-0C13-48A9-97A8-2D4368944D02 2313E516-3568-462B-A61A-E984199DD093 outgoing 1
B26D6268-5769-5023-09C9-01049748D4BD 369A101E-FEE2-4EAE-86BD-5FA52D745080 0661993C-B04F-41FD-9906-95AD96356A02 outgoing 0
2B5C8C76-D8A1-773F-AF09-0104AEEB573A 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 incoming 0
8E793057-CB5A-492D-0AEF-0105920C40FB 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 incoming 1
1C760FD8-B652-E9E2-1E8C-0105E4AFAC03 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 0
3BA60361-DB5E-0912-D441-0108E5D3F9D0 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 1
AFE80CEA-104F-61BC-30D6-010D3096C775 00000000-0000-0000-0000-000000000000 00000000-0000-0000-0000-000000000000 outgoing 0
963DA531-2707-D6A8-42B1-01145AE46447 9CADA981-C63E-45BF-9BC3-26981DB586E3 514E5977-7DE8-48D4-AF00-6A7AA795EAB1 outgoing 1

[Trojden]

@amitchandak 

I have prepared better T-SQL, have a look:

declare @FCRCOUNT decimal (18,2)
declare @totalCalls decimal (18,2)
declare @FCRpercentage decimal (18,2)

set @FCRCOUNT =(
	select COUNT(*) from 
		(select CaseKey, max(WorkflowStateKey) Workflow,count(*) Workflow_COUNT, Direction from POWER_BI_AGENTS_OUTGOING_CALLS
		group by CaseKey, Direction
		HAVING COUNT(*) =1) FCR
	inner join POWERBI_WORKFLOW_STATES WS on WS.WorkflowStateKey = FCR.status
	where WS.Qualifier =5 AND FCR.Direction = 'Outgoing')
	
set @totalCalls = (select count(*) from POWER_BI_AGENTS_OUTGOING_CALLS where Direction = 'Outgoing')

set @FCRpercentage=(@FCRCOUNT / @totalCalls)
select FORMAT(@FCRpercentage,'P0') as FCR