Create a best model out from a specific dataset

[jithinrg]

Hi team,

I would like to model this dat in PowerBI.

Scenario: Need to identify the actual sales of each employee listed in the Employee table.

In the sales table, tmp ids are distributed over multiple columns (and multiple emp ids in each with comma delimited) which we need to merge and transpose.

Note: emp table has duplicate emp ids means some emps have to manage multiple teams.

Any help would be greatly appreciated.

[vanessafvg · Accepted solution]

see attched.  you will need to play with the visual more to get what you want but please see if this is what you were after.

[jithinrg]

Thank you @vanessafvg ..I will play around with the model you shared and let you know.

[v-yiruan-msft]

Hi @jithinrg ,

Is there anything else you need help with regarding this post? If not, could you please mark the helpful post as Answered? It will help the others in the community find the solution easily if they face the same problem as yours. Thank you.

Best Regards

[jithinrg]

hi @vanessafvg ./..thanks for your reply. I'm not sure where to load the files. i have copied the table below.

Fact table
SaleID	PartnersID	TeamLeadsID	ManagersID	ConsultantsID	Sale value	Sale date	RegionName
1000-10	EMP01,EMP03	EMP10,EMP11,EMP45			60000	02/03/2021	West
1000-20	EMP09,EMP98,EMP67		EMP456,EMP765	EMP334,EMP776	100000	06/07/2000	East
1000-10	EMP01,EMP03	EMP555,EMP666,EMP888	EMP309,EMP845	EMP111,EMP222,EMP333	-60000	02/03/2021	West-Elim
1000-10	EMP01,EMP03	EMP10,EMP11,EMP45			60000	02/03/2021	West-Elim
2000-80	EMP34			EMP4567	403889	03/12/2021	Central
3000-50	EMP87,EMP899	EMP1000,EMP447			3000000	04/09/2000	North

Employee dim table (only employees in Sales team)
EmpID	EmployeeName	Team	SaleTarget
EMP01	A	Team 1	10000
EMP09	B	Team 2	30000
EMP34	C	Team 3	50000
EMP87	D	Team 4	60000
EMP03	E	Team 5	873333
EMP98	F	Team 6	67999
EMP67	G	Team 7	30000
EMP10	H	Team 8	40000
EMP555	I	Team 9	50000
EMP555	I	Team 10	80000
EMP1000	J	Team 11	90000
EMP11	K	Team 12	10011
EMP666	L	Team 13	40000
EMP447	M	Team 14	500000
EMP45	N	Team 15	7800000
EMP888	O	Team 16	4500000

[vanessafvg]

so if i understand correctly

we are combining partnersid, teamleadsid and consultants id into one column with a unique set of empid's?

then we want each empid to have its own   line?

then how will that look on each line after the data has transposed  

sale id ,empid, sale, value , sale date,  region name and then each empid has it own line with the full sales value?

when  posting  a question its always best to given an example of what you want the result to look like so its clearly understood

[jithinrg]

you are right.

please see the snips below.

Stage 1 merging and splitting

stage 2 transposing

transosing costs the row count, which we needs to eliminated in later stage.

Actually i would like to know what are the best approach to model this kind of dataset and would like to measure each employee's actual sale details vs thier targets.

apologies for not posting the examples.

[vanessafvg]

what field defines who made the sale, the partners? consultant?  if there are multiple employees per sale, you would still attribue the total sales value per sales person?

[jithinrg]

the list of empids in each column are a part of the sale...hopefully we have to combine all empid fields to make a field for all empids and transpose? not sure whether that approach is correct.

[vanessafvg]

can you provide the data in text format?