Multiple two columns tables with many to many relationships

[Jason2500]

Hi

I am having two tables (Sales Table & Commission Table) and the two was connected with many to many relationships as both the table doesn't have unique values.

I connected both the tables with Name as a Many to Many relationships dut to non-unique values. I wanted to multiply target and commission and need below expected output

I used below formula to calculate commission
Commission = SUM('Sales'[Total]) * SUM('Commission'[Commission (%)])

The above formula is working, but I am not able to get the commission in the output table. Please advise

[Anonymous]

@Jason2500 

Did you miss some conditions or data., the expected output is logically not possible for Ron and Mitty, because there is no respective commission for the 2 names.

Paul

[parry2k]

@Jason2500 better approach would be to create a common table for the name as a dimension in Power Query and set a relationship with this new dimension table with these two tables and then everything will work. It is better to create a dimension as a scalable and best practice solution.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡