I have two tables. one table has unique values while the other table has records based on those unique values. I added them to a data model and then made a pivot table. my intention was to find sum of each rate from the first table for each unique ID and name from the second table. however the sum is all the same instead of the sum to each unique ID.

what am i doing wrong? how do i get to my “desired pivot”?

enter image description here

I tried changing the relationships and moved around the items in the rows and columns of the pivot.