Obtaining target by branch, category, agent and month.

[User_790790]

I need to show sales against target in PowerBi by Agent ID , Branch & Category 1. Even if the customer has no sale in a particular category.

This is the sales data

Date	Category	Category- 1	Branch Code	Agent ID	Designation	Sales
01-02-24	Post	Post-VOICE	PRL	AB017	Sales Rep	27
01-02-24	Post	Post-DATA	PRL	AB017	Sales Rep	2 0
01-02-24	Post	Post-DATA	PAC	AB013	Sales Manager	21
01-02-24	Post	Post-VOICE	OAC	AB027	Sales Rep	22
02-02-24	Post	Post-DATA	OAC	AB027	Sales Rep	24
02-02-24	Post	Post-VOICE	OAC	AB027	Sales Rep	10
01-02-24	Pre	Pre-DATA	OAC	AB027	Sales Rep	11
01-02-24	Pre	Pre-VOICE	PRL	AB017	Sales Rep	13
01-02-24	Z	Z	PAC	AB013	Sales Manager	21
01-02-24	Z	Z	OAC	AB027	Sales Rep	24
02-02-24	Post	Post-VOICE	PRL	AB017	Sales Rep	21
02-02-24	Post	Post-DATA	PRL	AB017	Sales Rep	18
02-02-24	Post	Post-DATA	PAC	AB013	Sales Manager	22
02-02-24	Post	Post-VOICE	OAC	AB027	Sales Rep	21
02-02-24	Post	Post-VOICE	OAC	AB027	Sales Rep	15
02-02-24	Post	Post-VOICE	OAC	AB027	Sales Rep	18
02-02-24	Pre	Pre-VOICE	OAC	AB027	Sales Rep	16
02-02-24	Pre	Pre-VOICE	PRL	AB017	Sales Rep	13
02-02-24	Z	Z	PAC	AB013	Sales Manager	11
02-02-24	Z	Z	OAC	AB027	Sales Rep	21
02-02-24	Z	Z	PRL	AB017	Sales Rep	21
02-02-24	Pre	Pre-DATA	PRL	AB017	Sales Rep	10
02-02-24	Pre	Pre-Data	PAC	AB013	Sales Manager	26
02-02-24	Post	Post-Voice	PAC	AB013	Sales Manager	12

This is the target data

Month-Year	Branch	Category	Category - 1	Designation	Target
Feb-23	PRL	Post	Post-VOICE	Sales Rep	60
Feb-23	PRL	Post	Post-DATA	Sales Rep	60
Feb-23	PRL	Pre	Pre-DATA	Sales Rep	64
Feb-23	PRL	Pre	Pre-VOICE	Sales Rep	51
Feb-23	PRL	Z	Z	Sales Rep	60
Feb-23	OAC	Post	Post-VOICE	Sales Rep	150
Feb-23	OAC	Post	Post-DATA	Sales Rep	64
Feb-23	OAC	Pre	Pre-DATA	Sales Rep	55
Feb-23	OAC	Pre	Pre-VOICE	Sales Rep	60
Feb-23	OAC	Z	Z	Sales Rep	69
Feb-23	PAC	Post	Post-VOICE	Sales Manager	30
Feb-23	PAC	Post	Post-DATA	Sales Manager	61
Feb-23	PAC	Pre	Pre-DATA	Sales Manager	52
Feb-23	PAC	Pre	Pre-VOICE	Sales Manager	62
Feb-23	PAC	Z	Z	Sales Manager	63

Desired Output : - I require it in this format so I that I can create a visualtion and few calculations.

Agent ID	Branch	Category-1	Sales	Target
AB027	OAC	Post-DATA	24	64
AB027	OAC	Post-VOICE	86	150
AB027	OAC	Pre-DATA	11	55
AB027	OAC	Pre-VOICE	16	60
AB027	OAC	Z	45	69
AB017	PRL	Post-DATA	38	60
AB017	PRL	Post-VOICE	48	60
AB017	PRL	Pre-DATA	10	64
AB017	PRL	Pre-VOICE	26	51
AB017	PRL	Z	21	60
AB013	PAC	Post-DATA	43	61
AB013	PAC	Post-VOICE	12	30
AB013	PAC	Pre-DATA	26	52
AB013	PAC	Pre-VOICE	62
AB013	PAC	Z	32	63

[v-kongfanf-msft]

Hi @User_790790 ,

Based on the information you have provided, it is not possible to establish a relationship between the sales and target tables by specific fields.

Best Regards,
Adamk Kong

[audreygerred]

Hello! You should structure the data so that it follows star schema (fact and dimension), here is a blog post I wrote that explains it in more detail: http://powerbiwithme.com/2023/07/30/the-star-schema-edition/

For your model, you will want to have a category dimension table that has Category 1 and Category. Ideally you would have surrogate keys and such for best practice, but for now, you can just use Category 1 as your key - you would join Category 1 from your new dimension table (this dim table will have Category 1 listed one time only and the corresponding category, this way Category 1 is unique in the dim table) to each of your tables. 

You would also need a date dim table: http://powerbiwithme.com/2023/08/01/the-custom-date-table-edition/

One potential issue I see as the data is now is that you want to show targets by AgentID, but AgentID is not part of the target table. 

You will also need a Branch table that then joins to each of your tables. And, and Agent dimension table (with AgentID also added into the target table if you plan to use AgentID to show targets).

Once you have this and you make your visual, you would grab Agent ID from the Agent dim table, Branch from the Branch dim table, and Category from the Category dim table.

Then, you would pull in your measures for Sales and Target: Sales = SUM('SalesTable'[Sales]) and Target = SUM('TargetTable'[Target])

As of right now, target would not work if you had AgentID in a visual since Target table is not at that level of granularity.

[User_790790]

Hey @audreygerred , 

Thanks for your response. Regarding the agent target, I receive the target data like this:

eg. for branch - oac , category 1 - z , designation - sales rep, target - 69.

so all the sales rep in the branch for that particular category will have that target.

If add an Agent ID column , it would be a very large dataset. Since I would have to add agent id by 5 categories and targets . In my original dataset I have about 80+ agents and I have to consider each month as well.

For eg. 

Month	Agent ID	Branch	Category	Category-1	Target
Feb-24	AB027	OAC	Post	Post-DATA	64
Feb-24	AB027	OAC	Post	Post-VOICE	150
Feb-24	AB027	OAC	Pre	Pre-DATA	55
Feb-24	AB027	OAC	Pre	Pre-VOICE	60
Feb-24	AB027	OAC	Z	Z	69
Mar-24	AB027	OAC	Post	Post-DATA	62
Mar-24	AB027	OAC	Post	Post-VOICE	130
Mar-24	AB027	OAC	Pre	Pre-DATA	51
Mar-24	AB027	OAC	Pre	Pre-VOICE	67
Mar-24	AB027	OAC	Z	Z	63

Should I take this approach or is there an alternative i could consider?

[audreygerred]

Yes, if you want to report your target by Agent ID, you will need the target by Agent ID. Although it may seem like a lot of data, it really isn't. Power BI is great with compression.