Summing distinct values by criteria in another column

UserMJY

The data in my ETL is double counting values as one user can create multiple opportunities in multiple categories. I need to be able to sum the unique value of these opportunities by user and category . For example; in Category A, UserX can generate 4 opportunities to the value of $1000 each. I need the $1000 to be accounted for only once for Category A. User X can also generate 2 opportunities in Category B to the value of $2000 each. I would need the $1000 and $2000 for UserX to be accounted for only once each.

I have created a unique code in the ETL by CONCAT('Category', 'User', 'Opportunity Value') and then used a beast mode: Sum(DISTINCT(Case when 'unique code' is not null then 'Opportunity Value' end)). This gives me the correct values but my totals all sum incorrectly.

Is there a better way to do this? Why are my totals not giving the correct value and how can I solve this?