Display KPI Summary

[tanmaylokhande123]

Hi,

I have 3 Shops(Trim ,Engine ,Transmission) and each shop has 7 KPIs(Currently used 1 KPI in excel file ) and each KPI has 3 Sub-KPIs(Currently used 1  Sub-KPI in excel file ) in it.

I want to show Summary of these Shops along with there KPIs and Sub-KPIs across there Targets and Actual values of Past 3 Days ,current month and past 2 Months

Below I have provided Excel containing Data and its desired output.

Please help in getting this desired output.

Regards,

Tanmay

[JHuis]

When using a Pivot table You can add 

Shop

KPI

Sub-KPI

Date

Unit

as dimensions

Date as a Horizontal dimension

Target 

 as an measure. 

This will do the trick 😉

[tanmaylokhande123]

Thanks for Replying . However I tried your solution but still not getting desired output.

Below I have added output as per your suggestion  and desired Output in excel file .

Please help me to get desired output.

Regards,

Tanmay

After using provided trick:

Shop	KPI	Sub-KPI	Date	Unit	Target	Actuals	Unable to show Past 3 Months here
Trimming	Production	Volume Produced	04-04-2022	Nos	21675	21675
			05-04-2022	Nos	21675	21665
			06-04-2022	Nos	21675	21685
Engine	Production	Volume Produced	04-04-2022	Nos	21600	21600
			05-04-2022	Nos	21600	21595
			06-04-2022	Nos	21600	21605
Transmission	Production	Volume Produced	04-04-2022	Nos	3575	3570
			05-04-2022	Nos	3575	3580
			06-04-2022	Nos	3575	3575

[tanmaylokhande123]

Below I have provided data set:

Date	Shop	Target FAI	Target LTA	Target BC Manpower	Target White Collar	Target Efficiency	Target Productivity	Target Direct HPV	Target OEE	Target FTQ	Target Volume Produce	Target Power Consumption	FAI	LTA	BC Manpower	White Collar	Efficiency	Productivity	Direct HPV	OEE	FTQ	Power Consumption
06-06-2022	Trimming	0	0	82	19	95%	61%	6	85%	93%	1698	130	0	0	155	19	95%	60%	6	80%	92%	190
06-06-2022	Engine	0	0	525	19	95%	75%	5	85%	93%	17163	153	0	0	525	19	90%	70%	4	80%	90%	150
06-06-2022	Transmission	0	0	1576	35	95%	80%	13.3	85%	93%	17143	32	0	0	1576	34	90%	79%	13	85%	93%	33
07-06-2022	Trimming	0	0	82	19	95%	61%	6	85%	93%	1698	130	0	0	155	19	95%	60%	6	67%	72%	140
07-06-2022	Engine	0	0	525	19	95%	75%	5	85%	93%	17163	153	0	0	520	19	93%	65%	5	75%	90%	151
07-06-2022	Transmission	0	0	1576	35	95%	80%	13.3	85%	93%	17143	32	0	0	1570	30	91%	79%	13	81%	80%	33
08-06-2022	Trimming	0	0	82	19	95%	61%	6	85%	93%	1698	130	0	0	155	19	95%	60%	6	67%	72%	200
08-06-2022	Engine	0	0	525	19	95%	75%	5	85%	93%	17163	153	1	0	520	19	90%	75%	5	85%	90%	160
08-06-2022	Transmission	0	0	1576	35	95%	80%	13.3	85%	93%	17143	32	0	0	1570	34	91%	79%	13	80%	80%	30

Following  is expected Output.

SUMMARY DATE	KPI	SUB KPI	UNITS	TARGET	DATE1	DATE2	DATE3	MONTH1	MONTH2	MONTH3
06-06-2022	KPI	SUB KPI	UNITS	TARGET	04-06-2022	05-06-2022	06-06-2022	Jun-22	May-22	Apr-22
06-06-2022	SAFETY	FAI	NOS.	0	0	0	0	1	0	0
06-06-2022	SAFETY	LTA	NOS.	0	0	0	0	0	0	0
06-06-2022	PEOPLE	BC MANPOWER	NOS.	143	146	146	151	149	151	144
06-06-2022	PEOPLE	ABSENTEEISM	%	5	5	12	6	5	5	5
06-06-2022	PEOPLE	WHITE COLLAR	NOS.	19	18	19	19	19	19	19
06-06-2022	PRODUCTION	VOLUME PRODUCED	NOS.	2537	86	85		438	1866	2000
06-06-2022	PRODUCTION	ACTUAL JPH	JPH	8	5	6		6	8	8
06-06-2022	PRODUCTION	B-X104 JPH	JPH	28	24	33	0	20	27	28
06-06-2022	PRODUCTIVITY	EFFICIENCY	%	95%	68	65		69	54	69
06-06-2022	PRODUCTIVITY	PRODUCTIVITY	%	61%	32	31	0	45	44	61
06-06-2022	PRODUCTIVITY	DIRECT HPV	HPV	6	11.2	11.6		8	8.1	5.1
06-06-2022	PRODUCTIVITY	OEE	%	85%	50	34	0	44	48	64
06-06-2022	QUALITY	FTQ	%	93%	92%	90%		90%	92%	94%
06-06-2022	COST	SPARE CONSUMPTION	INR/CAR	250	177	1061		135	320	203
06-06-2022	COST	POWER CONSUMPTION	KWH/CAR	130	267	326		128	155	172
06-06-2022	COST	CONSUMABLE	INR/CAR	145	93	236		123	146	94
06-06-2022	COST	PROCESS REJECTION	INR/CAR	12	0	0		27	63	33
06-06-2022	COST	VENDOR REJECTION	INR/CAR	100	0	138		24	153	66
07-06-2022	KPI	SUB KPI	UNITS	TARGET	05-06-2022	06-06-2022	07-06-2022	Jun-22	May-22	Apr-22
07-06-2022	SAFETY	FAI	NOS.	0	0	0	0	1	0	0
07-06-2022	SAFETY	LTA	NOS.	0	0	0	0	0	0	0
07-06-2022	PEOPLE	BC MANPOWER	NOS.	143	146	146	151	149	151	144
07-06-2022	PEOPLE	ABSENTEEISM	%	5	5	12	6	5	5	5
07-06-2022	PEOPLE	WHITE COLLAR	NOS.	19	18	19	19	19	19	19
07-06-2022	PRODUCTION	VOLUME PRODUCED	NOS.	2537	86	85		438	1866	2000
07-06-2022	PRODUCTION	ACTUAL JPH	JPH	8	5	6		6	8	8
07-06-2022	PRODUCTION	B-X104 JPH	JPH	28	24	33	0	20	27	28
07-06-2022	PRODUCTIVITY	EFFICIENCY	%	95%	68	65		69	54	69
07-06-2022	PRODUCTIVITY	PRODUCTIVITY	%	61%	32	31	0	45	44	61
07-06-2022	PRODUCTIVITY	DIRECT HPV	HPV	6	11.2	11.6		8	8.1	5.1
07-06-2022	PRODUCTIVITY	OEE	%	85%	50	34	0	44	48	64
07-06-2022	QUALITY	FTQ	%	93%	92%	90%		90%	92%	94%
07-06-2022	COST	SPARE CONSUMPTION	INR/CAR	250	177	1061		135	320	203
07-06-2022	COST	POWER CONSUMPTION	KWH/CAR	130	267	326		128	155	172
07-06-2022	COST	CONSUMABLE	INR/CAR	145	93	236		123	146	94
07-06-2022	COST	PROCESS REJECTION	INR/CAR	12	0	0		27	63	33
07-06-2022	COST	VENDOR REJECTION	INR/CAR	100	0	138		24	153	66
08-06-2022	KPI	SUB KPI	UNITS	TARGET	06-06-2022	07-06-2022	08-06-2022	Jun-22	May-22	Apr-22
08-06-2022	SAFETY	FAI	NOS.	0	0	0	0	1	0	0
08-06-2022	SAFETY	LTA	NOS.	0	0	0	0	0	0	0
08-06-2022	PEOPLE	BC MANPOWER	NOS.	143	146	146	151	149	151	144
08-06-2022	PEOPLE	ABSENTEEISM	%	5	5	12	6	5	5	5
08-06-2022	PEOPLE	WHITE COLLAR	NOS.	19	18	19	19	19	19	19
08-06-2022	PRODUCTION	VOLUME PRODUCED	NOS.	2537	86	85		438	1866	2000
08-06-2022	PRODUCTION	ACTUAL JPH	JPH	8	5	6		6	8	8
08-06-2022	PRODUCTION	B-X104 JPH	JPH	28	24	33	0	20	27	28
08-06-2022	PRODUCTIVITY	EFFICIENCY	%	95%	68	65		69	54	69
08-06-2022	PRODUCTIVITY	PRODUCTIVITY	%	61%	32	31	0	45	44	61
08-06-2022	PRODUCTIVITY	DIRECT HPV	HPV	6	11.2	11.6		8	8.1	5.1
08-06-2022	PRODUCTIVITY	OEE	%	85%	50	34	0	44	48	64
08-06-2022	QUALITY	FTQ	%	93%	92%	90%		90%	92%	94%
08-06-2022	COST	SPARE CONSUMPTION	INR/CAR	250	177	1061		135	320	203
08-06-2022	COST	POWER CONSUMPTION	KWH/CAR	130	267	326		128	155	172
08-06-2022	COST	CONSUMABLE	INR/CAR	145	93	236		123	146	94
08-06-2022	COST	PROCESS REJECTION	INR/CAR	12	0	0		27	63	33
08-06-2022	COST	VENDOR REJECTION	INR/CAR	100	0	138		24	153	66

When I select any Summary date i get summary for that specific date e.g. for 6/6/2022:

SUMMARY DATE	KPI	SUB KPI	UNITS	TARGET	DATE1	DATE2	DATE3	MONTH1	MONTH2	MONTH3
06-06-2022	KPI	SUB KPI	UNITS	TARGET	04-06-2022	05-06-2022	06-06-2022	Jun-22	May-22	Apr-22
06-06-2022	SAFETY	FAI	NOS.	0	0	0	0	1	0	0
06-06-2022	SAFETY	LTA	NOS.	0	0	0	0	0	0	0
06-06-2022	PEOPLE	BC MANPOWER	NOS.	143	146	146	151	149	151	144
06-06-2022	PEOPLE	ABSENTEEISM	%	5	5	12	6	5	5	5
06-06-2022	PEOPLE	WHITE COLLAR	NOS.	19	18	19	19	19	19	19
06-06-2022	PRODUCTION	VOLUME PRODUCED	NOS.	2537	86	85		438	1866	2000
06-06-2022	PRODUCTION	ACTUAL JPH	JPH	8	5	6		6	8	8
06-06-2022	PRODUCTION	B-X104 JPH	JPH	28	24	33	0	20	27	28
06-06-2022	PRODUCTIVITY	EFFICIENCY	%	95%	68	65		69	54	69
06-06-2022	PRODUCTIVITY	PRODUCTIVITY	%	61%	32	31	0	45	44	61
06-06-2022	PRODUCTIVITY	DIRECT HPV	HPV	6	11.2	11.6		8	8.1	5.1
06-06-2022	PRODUCTIVITY	OEE	%	85%	50	34	0	44	48	64
06-06-2022	QUALITY	FTQ	%	93%	92%	90%		90%	92%	94%
06-06-2022	COST	SPARE CONSUMPTION	INR/CAR	250	177	1061		135	320	203
06-06-2022	COST	POWER CONSUMPTION	KWH/CAR	130	267	326		128	155	172
06-06-2022	COST	CONSUMABLE	INR/CAR	145	93	236		123	146	94
06-06-2022	COST	PROCESS REJECTION	INR/CAR	12	0	0		27	63	33
06-06-2022	COST	VENDOR REJECTION	INR/CAR	100	0	138		24	153	66

Please help.

Regards,

Tanmay