Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
Proud to be a Super User!
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.
Proud to be a Super User!
Thank 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
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 |
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
Proud to be a Super User!
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.
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?
Proud to be a Super User!
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.
can you provide the data in text format?
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |