Stop Values from Aggregating

[MWilliams]

Hi, I am trying to create a matrix in which employee hours are tracked by work category.  There is a value that shows percentage of hours per work category out of employees total hours.  I would then like to multiple the employees salary, 401k, and tax values by this percentage in order to get salary/401k/tax per work category for each employee.  See below image for an example of what I want:  

What I keep ending up with is the below screenshot:

I have the salary, 401k, and tax information for each employee in a seperate excel file, see screenshot below (Forgot column headers but the order is: Employee Name, Work Category, Salary, Tax, 401k).  

Does anyone have any advice on how to achieve my desired result?  

Thank you!

Edit:  I should add that I have already played around with the summarization settings.  Don't summarize is not an option.

[v-danhe-msft]

Hi @MWilliams,

From your data and description, it could work normally on my side:

Could you please share your pbix file to have a test if possible?

Regards,

Daniel He

[MWilliams]

Thank you so much for trying to help @v-danhe-msft!  My PBIX is in the following link: https://drive.google.com/file/d/17WvTpS29TK6dMo3gdKd-lIhGY4-JW_7K/view?usp=sharing

[v-danhe-msft]

Hi @MWilliams,

It seemed the format of your [Salary], [Tax] and [401k] showed as text, you need to switch them to number:

Then I could not figure out the relationships you have created in your pbix, I used the 'Business Unit Master' [Column1] to show the subcolumn:

Hope it could help you.

Regards,

Daniel He

[MWilliams]

@v-danhe-msft Your output has the same problem mine has.  

 

The 4090 should be split up proportionally to the hours.  I.E. for Category 41 Salary should be roughly 2550 and for Category 45 roughly 1540.  

As for my relationships, all I did was create separate sheets as masters of ResourceName and Categories because they were not unique within any other data sources.  

Edit: to be clear, my problem is two fold.  The first is getting salary, tax, and 401k to show up proportionally per category.  The second is to fix summarization.  4090 is the entirely wrong number, the values are summarizing as first and don't summarize is not an option.