Create a mapping table between 3 tables which have Many-to-Many relationships among all tables?

[Tejaswini_Dahat]

Hello all,

I want to calculate the Impact Amount for each client that is based on activities that the client does. Followed by flow and impact. I have 3 tables that do not have unique columns that can be used to create relationships for Tables A to C. Every column has redundant values that are causing to have Many-to-Many relationship in PowerBI. These Tables follow a hierarchy in order to calculate the Impact amount for each Client. Data hierarchy of the tables
These are my tables.

Your help is appreciated thanks!

[v-cherch-msft · Accepted solution]

Hi @Tejaswini_Dahat 

You may create 2 measures as below:

Measure = SUM(TableA[Activity Amount])*SUM(TableB[Flow Multiplier])*SUM(TableC[Impact Multiplier])

Measure 2 = SUMX(TableB,[Measure])

Regards,