Compare Sales vs Target Each Month (Multiple Targets by Branch & Other Categories)

[wmf_07]

In my dataset, I have my sales data and target data. The target data varies from month to month. I have multiple dates and multiple targets for each branch and Category. What would be the best way to model the data so I can show sales vs target?

For Example : - Filter by Month , Branch BA by Dairy,Cheese -> Sales v Target so I can the sales vs target for whichever month I filter it by.

Sales Data

Date	Salesman ID	Branch Code	Category	Category_1	Transaction No.	Sales
01-02-2024 00:00	10010020	A	Spices	Cloves	1006173020	23
01-02-2024 00:00	10010025	B	Satchets	Sauce	1005732860	45
01-02-2024 00:00	10010089	SD	Dairy	Milk	1006173020	6
01-02-2024 00:00	10010089	BA	Dairy	Cheese	1006806352	52
01-02-2024 00:00	10010030	WE	Frozen	Fries	1005323662	2
01-02-2024 00:00	10010020	A	Frozen	Vegetables	1006806352	1
01-02-2024 00:00	10010020	B	Spices	Cloves	1007731745	6
01-02-2024 00:00	10010020	SD	Satchets	Sauce	2758681741	7

Target (Overall)

Month	Branch	Category	Category_1	Target
Feb	A	Spices	Cloves	49
Feb	B	Satchets	Sauce	34
Feb	SD	Dairy	Milk	45
Feb	BA	Dairy	Cheese	45
Feb	WE	Frozen	Fries	41
Mar	A	Frozen	Vegetables	49
Mar	B	Spices	Cloves	39
Mar	SD	Satchets	Sauce	38
Mar	BA	Dairy	Cheese	43
Mar	WE	Dairy	Cheese	30

[Anonymous]

Hi @wmf_07 

Thanks for the reply from @lbendlin , please allow me to provide another insight:

I am a bit confused about your data, there is no data for March in the "Sales Data" table.

You might try the following:

You can merge the two tables together.

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Get your date columns to use the same format. Use a Calendar table. Be more specific on your targets.  Normalize the Branch, Category and Category_1 columns out into their own dimensions.