Forum Discussion
Helper ICreate a best model out from a specific dataset
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.
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.
9 Replies
- vanessafvg
Community Champion
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.
jithinrgThank you vanessafvg ..I will play around with the model you shared and let you know.
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
- vanessafvg
can you provide the data in text format?
- 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
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
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.
