Data Model in Many to Many Relationship

[patrickrw99]

Dear PBI community,

I would like to ask for help regarding my problem. I have two data sources:

1. Author-Revenue Proportion which defines how much revenue is split into different authors based on their books

2. the Revenue of each book by month.

Since 1 author can create many books and one book can be written by multple authors, this cause the data relationship to be many-to-many. 

My question to you all is, how do we create a measure to determine the revenue earned from each author? I have provided and excel file for reference, showing the 2 data sources as well as the expected table visual result that I am expecting. This is usually simple with one-to-many relationship but with many-to-many i cant seem to get the correct answer, especially for authors with multple books. 
Any help is appreciated, thank you!

File: https://docs.google.com/spreadsheets/d/1_hRKT2NrwelFC1Sb25sPuEKl_ti8W-Ko/edit?usp=sharing&ouid=11356...

[parry2k · Accepted solution]

@patrickrw99 solution is attached, tweak it as you see fit

✨ Follow us on LinkedIn and  to our YouTube channel

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.