target versus achieved

[srkase]

Hi all

YEAR	MINOR GROUP	PRODUCT	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	TOTAL QTY	TOTALVAL	month
20212022	ASMSP	425PS	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	APR
20212022	MINI	3SH	12	0	0	0	0	0	0	0	0	0	0	0	12	35205	APR
20212022	MINI	3SH	1	0	0	0	0	0	0	0	0	0	0	0	1	15648	APR
20212022	MINI	4SH	1	0	0	0	0	0	0	0	0	0	0	0	1	16549	APR
20212022	MINI	4SH	3	0	0	0	0	0	0	0	0	0	0	0	3	62442	APR
20212022	MINI	5SH	1	0	0	0	0	0	0	0	0	0	0	0	1	21715	APR
20212022	CBC	BCC	1	0	0	0	0	0	0	0	0	0	0	0	1	22076	APR
20212022	CBC	BCC	2	0	0	0	0	0	0	0	0	0	0	0	2	50267	APR
20212022	CBC	BCC	1	0	0	0	0	0	0	0	0	0	0	0	1	25980	APR
20212022	ASMSP	425PS	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	DEC
20212022	MINI	3SH	12	0	0	0	0	0	0	0	0	0	0	0	12	35205	DEC
20212022	MINI	3SH	1	0	0	0	0	0	0	0	0	0	0	0	1	15648	DEC
20212022	MINI	4SH	1	0	0	0	0	0	0	0	0	0	0	0	1	16549	DEC
20212022	MINI	4SH	3	0	0	0	0	0	0	0	0	0	0	0	3	62442	DEC
20212022	MINI	5SH	1	0	0	0	0	0	0	0	0	0	0	0	1	21715	DEC
20212022	CBC	BCC	1	0	0	0	0	0	0	0	0	0	0	0	1	22076	DEC
20212022	CBC	BCC	2	0	0	0	0	0	0	0	0	0	0	0	2	50267	DEC
20212022	CBC	BCC	1	0	0	0	0	0	0	0	0	0	0	0	1	25980	DEC
20222023	ASMSP	425PS	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	APR
20222023	MINI	3SH	2	0	0	0	0	0	0	0	0	0	0	0	2	35205	APR
20222023	MINI	3SH	3	0	0	0	0	0	0	0	0	0	0	0	3	15648	APR
20222023	MINI	4SH	4	0	0	0	0	0	0	0	0	0	0	0	4	16549	APR
20222023	MINI	4SH	5	0	0	0	0	0	0	0	0	0	0	0	5	62442	APR
20222023	MINI	5SH	6	0	0	0	0	0	0	0	0	0	0	0	6	21715	APR
20222023	CBC	BCC	7	0	0	0	0	0	0	0	0	0	0	0	7	22076	APR
20222023	CBC	BCC	8	0	0	0	0	0	0	0	0	0	0	0	8	50267	APR
20222023	CBC	BCC	9	0	0	0	0	0	0	0	0	0	0	0	9	25980	APR
20222023	ASMSP	425PS	10	0	0	0	0	0	0	0	0	0	0	0	10	5570	DEC
20222023	MINI	3SH	11	0	0	0	0	0	0	0	0	0	0	0	11	35205	DEC
20222023	MINI	3SH	12	0	0	0	0	0	0	0	0	0	0	0	12	15648	DEC
20222023	MINI	4SH	13	0	0	0	0	0	0	0	0	0	0	0	13	16549	DEC
20222023	MINI	4SH	14	0	0	0	0	0	0	0	0	0	0	0	14	62442	DEC
20222023	MINI	5SH	15	0	0	0	0	0	0	0	0	0	0	0	15	21715	DEC
20222023	CBC	BCC	16	0	0	0	0	0	0	0	0	0	0	0	16	22076	DEC
20222023	CBC	BCC	17	0	0	0	0	0	0	0	0	0	0	0	17	50267	DEC
20222023	CBC	BCC	18	0	0	0	0	0	0	0	0	0	0	0	18	25980	DEC
Required result, based on the conditions
Target for 20222023 = total qty of 20212022 + 80% for ASMSP	Target for 20222023 = total qty of 20212022 + 100% for CBC	Target for 20222023 = total qty of 20212022 + 150% for MINI
I want to keep the above target for each of the Minor group and measure its performace for current year
MINOR	20212022	Increase Target	Qty	Current year 20222023
ASMSP	2	80%	3.6	11
CBC	8	100%	16	75
MINI	36	150%	90	85

Thanks in advance ...

Regards

SRK

[v-shex-msft]

Hi @srkase,

I'd like to suggest you use unpivot columns feature to convert these month fields to attribute and value, then you can simply use measure expression to calculate and compare results based on current year and month values.

Unpivot columns (Power Query) (microsoft.com)

Regards,

Xiaoxin Sheng

[srkase]

Sorry,,, There is a last column in my data representing the month ... probably at the time of copying it might have been left out

here is my data

YEAR	MINOR GROUP	PRODUCT	APR	MAY	JUN	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR	TOTAL QTY	TOTALVAL	month
20212022	ASMSP	425PS	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	APR
20212022	MINI	3SH	12	0	0	0	0	0	0	0	0	0	0	0	12	35205	APR
20212022	MINI	3SH	1	0	0	0	0	0	0	0	0	0	0	0	1	15648	APR
20212022	MINI	4SH	1	0	0	0	0	0	0	0	0	0	0	0	1	16549	APR
20212022	MINI	4SH	3	0	0	0	0	0	0	0	0	0	0	0	3	62442	APR
20212022	MINI	5SH	1	0	0	0	0	0	0	0	0	0	0	0	1	21715	APR
20212022	CBC	BCC	1	0	0	0	0	0	0	0	0	0	0	0	1	22076	APR
20212022	CBC	BCC	2	0	0	0	0	0	0	0	0	0	0	0	2	50267	APR
20212022	CBC	BCC	1	0	0	0	0	0	0	0	0	0	0	0	1	25980	APR
20212022	ASMSP	425PS	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	DEC
20212022	MINI	3SH	12	0	0	0	0	0	0	0	0	0	0	0	12	35205	DEC
20212022	MINI	3SH	1	0	0	0	0	0	0	0	0	0	0	0	1	15648	DEC
20212022	MINI	4SH	1	0	0	0	0	0	0	0	0	0	0	0	1	16549	DEC
20212022	MINI	4SH	3	0	0	0	0	0	0	0	0	0	0	0	3	62442	DEC
20212022	MINI	5SH	1	0	0	0	0	0	0	0	0	0	0	0	1	21715	DEC
20212022	CBC	BCC	1	0	0	0	0	0	0	0	0	0	0	0	1	22076	DEC
20212022	CBC	BCC	2	0	0	0	0	0	0	0	0	0	0	0	2	50267	DEC
20212022	CBC	BCC	1	0	0	0	0	0	0	0	0	0	0	0	1	25980	DEC
20222023	ASMSP	425PS	1	0	0	0	0	0	0	0	0	0	0	0	1	5570	APR
20222023	MINI	3SH	2	0	0	0	0	0	0	0	0	0	0	0	2	35205	APR
20222023	MINI	3SH	3	0	0	0	0	0	0	0	0	0	0	0	3	15648	APR
20222023	MINI	4SH	4	0	0	0	0	0	0	0	0	0	0	0	4	16549	APR
20222023	MINI	4SH	5	0	0	0	0	0	0	0	0	0	0	0	5	62442	APR
20222023	MINI	5SH	6	0	0	0	0	0	0	0	0	0	0	0	6	21715	APR
20222023	CBC	BCC	7	0	0	0	0	0	0	0	0	0	0	0	7	22076	APR
20222023	CBC	BCC	8	0	0	0	0	0	0	0	0	0	0	0	8	50267	APR
20222023	CBC	BCC	9	0	0	0	0	0	0	0	0	0	0	0	9	25980	APR
20222023	ASMSP	425PS	10	0	0	0	0	0	0	0	0	0	0	0	10	5570	DEC
20222023	MINI	3SH	11	0	0	0	0	0	0	0	0	0	0	0	11	35205	DEC
20222023	MINI	3SH	12	0	0	0	0	0	0	0	0	0	0	0	12	15648	DEC
20222023	MINI	4SH	13	0	0	0	0	0	0	0	0	0	0	0	13	16549	DEC
20222023	MINI	4SH	14	0	0	0	0	0	0	0	0	0	0	0	14	62442	DEC
20222023	MINI	5SH	15	0	0	0	0	0	0	0	0	0	0	0	15	21715	DEC
20222023	CBC	BCC	16	0	0	0	0	0	0	0	0	0	0	0	16	22076	DEC
20222023	CBC	BCC	17	0	0	0	0	0	0	0	0	0	0	0	17	50267	DEC
20222023	CBC	BCC	18	0	0	0	0	0	0	0	0	0	0	0	18	25980	DEC