Target vs Achieved

[srkase]

Hi,

My data is as follows.

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
The above is my data.
Required result,
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

[Anonymous · Accepted solution]

Hi @srkase ,

In Power Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZWxjsIwEET/JTWFvfHaSQm+k6AARZcS8f+/cV5zkQg3JqxDCscUs09LMqO5XhsyZNOhZtfsx/M4pNsRD2O6bTpGcUTPHOTnfvhpbrsZ/Xy6nNLVjkeRkpYtAy2T4XfoFYtb9q57yXb1bM+uf4PdKtmi9+QcvWRz9d5kgy2873iI6XmIsRZNJvhltNYmomdDPmy0Nfddwd4fDM/Xd9wwPIv0isWn8JTZ68OzxF4TnjJ7fXgA+1PhWUCvCc8WW/+F5wGd/gjJd9imeSb6zN41LwX0DmTXWBD0zhP7bm+nZIse9A5ks5ItetA7T+x7dLySLXrQOxP70YRBiRY96B2E7pRo0YPeQeheiRY96J1idDRsMw38bx5ocKsOpgyA5sH0ql4D3QNNbrXpzAOgfTBdm888APoHhshqE5oHQAMhQ1ptRPMA6CAI14Y0D4AWgnBtTPPArIduvw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [YEAR = _t, #"MINOR GROUP" = _t, PRODUCT = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t, JAN = _t, FEB = _t, MAR = _t, #"TOTAL QTY" = _t, TOTALVAL = _t, month = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"YEAR", Int64.Type}, {"MINOR GROUP", type text}, {"PRODUCT", type text}, {"APR", Int64.Type}, {"MAY", Int64.Type}, {"JUN", Int64.Type}, {"JUL", Int64.Type}, {"AUG", Int64.Type}, {"SEP", Int64.Type}, {"OCT", Int64.Type}, {"NOV", Int64.Type}, {"DEC", Int64.Type}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"TOTAL QTY", Int64.Type}, {"TOTALVAL", Int64.Type}, {"month", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"YEAR", "MINOR GROUP", "TOTAL QTY"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Other Columns", {{"YEAR", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Other Columns", {{"YEAR", type text}}, "en-US")[YEAR]), "YEAR", "TOTAL QTY", List.Sum)
in
    #"Pivoted Column"

Then create measure:

Measure = 
var value1 = SELECTEDVALUE('Table'[20212022])
return
SWITCH(SELECTEDVALUE('Table'[MINOR GROUP]),"ASMSP",value1*1.8,"CBC",value1*2,"MINI",value1*2.5)

Best Regards,

Jay