I have 2 tables. Table1 has static data as below:
KPI Target
A >90% within 30 Business Days
B >90% within 60 Business Days
C >90% within 30 Business Days
D >90% within 60 Business Days
E >90% within 30 Business Days
F >90% within 60 Business Days
Table 2 has all the columns, calculated measures. There are measures (count & SLA%) for each KPI values.
ACTIVE NUMBER Breached Workflow Target Days CompletionDate_Final COMPLETION_DATE COMPLETION_DATE_Year COMPLETION_DATE_MonthName
0 RITM9 No A 30 Days Apr-24 01-04-2024 21:21 2024 Apr
0 RITM10 No E 30 Days Apr-24 18-04-2024 20:26 2024 Apr
0 RITM11 No B 60 Days Apr-24 11-04-2024 01:00 2024 Apr
0 RITM12 No A 30 Days Apr-24 23-04-2024 19:37 2024 Apr
0 RITM13 No 30 Days Apr-24 16-04-2024 17:30 2024 Apr
0 RITM14 No A 30 Days Apr-24 11-04-2024 16:47 2024 Apr
0 RITM15 No A 30 Days Apr-24 12-04-2024 20:24 2024 Apr
0 RITM16 No 60 Days Apr-24 30-04-2024 23:17 2024 Apr
0 RITM17 No A 30 Days Apr-24 11-04-2024 16:39 2024 Apr
0 RITM18 No A 30 Days Apr-24 11-04-2024 16:33 2024 Apr
0 RITM19 No D 60 Days Apr-24 11-04-2024 01:07 2024 Apr
0 RITM20 No B 60 Days Apr-24 12-04-2024 13:43 2024 Apr
0 RITM21 Yes B 60 Days Apr-24 11-04-2024 16:44 2024 Apr
0 RITM22 No B 60 Days Apr-24 25-04-2024 12:42 2024 Apr
0 RITM23 No 30 Days Apr-24 29-04-2024 18:27 2024 Apr
0 RITM24 Yes B 60 Days Apr-24 16-04-2024 14:57 2024 Apr
0 RITM25 No C 30 Days Apr-24 11-04-2024 00:18 2024 Apr
0 RITM26 No A 30 Days Apr-24 05-04-2024 16:14 2024 Apr
0 RITM27 Yes B 60 Days Apr-24 11-04-2024 00:36 2024 Apr
0 RITM28 No A 30 Days Apr-24 01-04-2024 21:31 2024 Apr
0 RITM29 Yes B 60 Days Apr-24 01-04-2024 21:32 2024 Apr
0 RITM30 No B 60 Days Apr-24 01-04-2024 21:33 2024 Apr
0 RITM31 No B 60 Days Apr-24 01-04-2024 21:34 2024 Apr
0 RITM32 Yes B 60 Days Apr-24 30-04-2024 22:24 2024 Apr
0 RITM33 Yes E 30 Days Apr-24 30-04-2024 22:19 2024 Apr
0 RITM34 No B 60 Days Apr-24 30-04-2024 21:56 2024 Apr
0 RITM35 No F 60 Days Apr-24 30-04-2024 23:30 2024 Apr
0 RITM36 Yes E 30 Days Apr-24 30-04-2024 23:40 2024 Apr
Table 2 data: measures calculation (Count & SLA%), same follows for B-F
SLA% for A = [Total Closed within SLA]/[Total Closed_30days]
[Total Closed within SLA] = CALCULATE(DISTINCTCOUNT(KPI_VW[NUMBER]),KPI_VW[Workflow] IN {“A”},KPI_VW[Target Days]=”30 Days”,KPI_VW[COMPLETION_DATE_Year]=Year(Now()),KPI_VW[COMPLETION_DATE_MonthName]=FORMAT(DATE(1, Month(Now())-1, 1), “MMM”),KPI_VW[Breached]=”No”)
[Total Closed_30days] = CALCULATE(DISTINCTCOUNT(KPI_VW[NUMBER]),KPI_VW[Workflow] IN {“A”},KPI_VW[Target Days]=”30 Days”,KPI_VW[COMPLETION_DATE_Year]=Year(Now()),KPI_VW[COMPLETION_DATE_MonthName]=FORMAT(DATE(1, Month(Now())-1, 1), “MMM”))
Count for A = CALCULATE(DISTINCTCOUNT(KPI_VW[NUMBER]),KPI_VW[Workflow] IN {“A”},KPI_VW[Target Days]=”30 Days”,KPI_VW[COMPLETION_DATE_Year]=Year(Now()),KPI_VW[COMPLETION_DATE_MonthName]=FORMAT(DATE(1, Month(Now())-1, 1), “MMM”))
I need to combine this 2 tables & the output should be as below, based on the matching KPI values:
KPI Target SLA Count
A >90% within 30 Business Days 91% 11
B >90% within 60 Business Days 100% 1
C >90% within 30 Business Days 100% 7
D >90% within 60 Business Days 21% 19
E >90% within 30 Business Days 100% 3
F >90% within 60 Business Days 100% 1
Any help is appreciated!