COUNTIF and SUMIF to calculate performance KPI

[Anonymous]

Hello guys,

I'm trying to build a performance KPI from the source table below.

I update it every week with a bunch of information and the "as of" column is the driver for the "WeekNum" column.

As of	Item	Responsible	Delivery Date	Late	WeekNum
10.aug.20	A	John	01.aug.20	1	33
10.aug.20	A	Cesar	05.aug.20	1	33
10.aug.20	B	Mark	10.jan.21	0	33
10.aug.20	E	Candace	05.feb.21	0	33
10.aug.20	T	Byron	07.sep.20	0	33
10.aug.20	E	Laura	08.nov.21	0	33
10.aug.20	E	Daniel	15.feb.20	1	33
10.aug.20	E	Daniel	05.jun.20	1	33
10.aug.20	E	Daniel	08.jun.20	1	33
10.aug.20	E	Candace	8.mar.20	1	33
10.aug.20	E	Byron	9.apr.21	0	33
10.aug.20	E	Byron	10.may.21	0	33
10.aug.20	E	Mark	18.may.20	1	33
17.aug.20	E	Laura	07.sep.20	0	34
17.aug.20	E	Candace	08.nov.21	0	34
17.aug.20	E	Laura	15.feb.20	1	34
17.aug.20	E	Cesar	05.jun.20	1	34
17.aug.20	E	Mark	08.jun.20	1	34
17.aug.20	E	John	8.mar.20	1	34
17.aug.20	E	Cesar	9.apr.21	0	34
17.aug.20	E	Mark	10.may.21	0	34
17.aug.20	E	Candace	10.jan.21	0	34
17.aug.20	E	Mark	07.sep.20	0	34
17.aug.20	E	Cesar	08.nov.21	0	34

To do so, i'm using CALCULATE + COUNTA and CALCULATE + SUM... but it's not working!

My expectation was to get a result table like this (to be added to the report page 😁😞

WeekNum	Total items	Late items	Performance
33	13	7	46%
John	1	1	0%
Cesar	1	1	0%
Mark	2	1	50%
Candace	2	1	50%
Byron	3	0	100%
Laura	1	0	100%
Daniel	3	3	0%
34	11	4	64%
John	1	1	0%
Cesar	3	1	67%
Mark	3	1	67%
Candace	2	0	100%
Byron	0	0	n/a
Laura	2	1	50%
Daniel	0	0	n/a

Total items : COUNTIF: number of items by name and week number

Late items : SUMIF: number of late items by name and week number

Performance : 1-(Late items / Total items)

Can you pleeeease help me with those DAX formulas? I'm a newbie at it and i'm trying for more than 3 days now 😅

Really appreciate your support!!

[mahoneypat · Accepted solution]

You should be able to make a matrix visual with your Week column and Responsible column in the rows, with these 3 measures in the Values area (replace Table with your actual table name).

Total Items = COUNT(Table[Item])

Late Items = CALCULATE([Total Items], Table[Late] = 1)

Performance = DIVIDE([Late Items], [Total Items])

(format Performance as percentage).

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hey Pat!

Worked like a charm!! Thanks for your prompt assistance! really appreciated!

BR,

Edson