Target vs Achieved

Question

Hi,My data is as follows.YEARMINOR GROUPPRODUCTAPRMAYJUNJULAUGSEPOCTNOVDECJANFEBMARTOTAL QTYTOTALVALmonth20212022ASMSP425PS10000000000015570APR20212022MINI3SH12000000000001235205APR20212022MINI3SH100000000000115648APR20212022MINI4SH100000000000116549APR20212022MINI4SH300000000000362442APR20212022MINI5SH100000000000121715APR20212022CBCBCC100000000000122076APR20212022CBCBCC200000000000250267APR20212022CBCBCC100000000000125980APR20212022ASMSP425PS10000000000015570DEC20212022MINI3SH12000000000001235205DEC20212022MINI3SH100000000000115648DEC20212022MINI4SH100000000000116549DEC20212022MINI4SH300000000000362442DEC20212022MINI5SH100000000000121715DEC20212022CBCBCC100000000000122076DEC20212022CBCBCC200000000000250267DEC20212022CBCBCC100000000000125980DEC20222023ASMSP425PS10000000000015570APR20222023MINI3SH200000000000235205APR20222023MINI3SH300000000000315648APR20222023MINI4SH400000000000416549APR20222023MINI4SH500000000000562442APR20222023MINI5SH600000000000621715APR20222023CBCBCC700000000000722076APR20222023CBCBCC800000000000850267APR20222023CBCBCC900000000000925980APR20222023ASMSP425PS1000000000000105570DEC20222023MINI3SH11000000000001135205DEC20222023MINI3SH12000000000001215648DEC20222023MINI4SH13000000000001316549DEC20222023MINI4SH14000000000001462442DEC20222023MINI5SH15000000000001521715DEC20222023CBCBCC16000000000001622076DEC20222023CBCBCC17000000000001750267DEC20222023CBCBCC18000000000001825980DEC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;The above is my data.&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Required result,&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Target for 20222023 = total qty of 20212022 + 80% for ASMSP&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Target for 20222023 = total qty of 20212022 + 100% for CBC&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Target for 20222023 = total qty of 20212022 + 150% for MINI&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;I want to keep the above target for each of the Minor group and measure its performace for current year&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MINOR20212022Increase TargetQtyCurrent year 20222023&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ASMSP280%3.611&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CBC8100%1675&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;MINI36150%9085&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Thanks in advance.&nbsp;Regards&nbsp;SRK&nbsp;

anonymous · Accepted Answer

Hi&nbsp;srkase&nbsp;,
&nbsp;
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)

&nbsp;
Best Regards,
Jay

Forum Discussion

Target vs Achieved

1 Reply

Related content

Calculate percentage of target achieved when target is a negative number

Average & achieve target

Calculate number need to achieve target %

TOP Achiever Analysis

Daily sales achievement based on monthly target

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