Forum Discussion

jithinrg's avatar
jithinrg
Icon for Helper I rankHelper I
4 years ago
Solved

Create 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 mult...
Need Help
  • vanessafvg's avatar
    vanessafvg
    4 years ago

    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's avatar
jithinrg
Icon for Helper I rankHelper I
4 years ago

 

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

 

Fact table       
SaleIDPartnersIDTeamLeadsIDManagersIDConsultantsIDSale valueSale dateRegionName
1000-10EMP01,EMP03EMP10,EMP11,EMP45  6000002/03/2021West
1000-20EMP09,EMP98,EMP67 EMP456,EMP765EMP334,EMP77610000006/07/2000East
1000-10EMP01,EMP03EMP555,EMP666,EMP888EMP309,EMP845EMP111,EMP222,EMP333-6000002/03/2021West-Elim
1000-10EMP01,EMP03EMP10,EMP11,EMP45  6000002/03/2021West-Elim
2000-80EMP34  EMP456740388903/12/2021Central
3000-50EMP87,EMP899EMP1000,EMP447  300000004/09/2000North

 

 

Employee dim table (only employees in Sales team) 
EmpIDEmployeeNameTeamSaleTarget
EMP01ATeam 110000
EMP09BTeam 230000
EMP34CTeam 350000
EMP87DTeam 460000
EMP03ETeam 5873333
EMP98FTeam 667999
EMP67GTeam 730000
EMP10HTeam 840000
EMP555ITeam 950000
EMP555ITeam 1080000
EMP1000JTeam 1190000
EMP11KTeam 1210011
EMP666LTeam 1340000
EMP447MTeam 14500000
EMP45NTeam 157800000
EMP888OTeam 164500000

 

 

 

vanessafvg's avatar
vanessafvg
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    jithinrg
    Icon for Helper I rankHelper I
    4 years ago

    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.