How to create measures between two tables that have a many to many relationship

[Anonymous]

I need to solve how to create measures between tables that have many to many relationships.

I’ll describe the process and data

• I have a table (TABLE A) that lists people and their allocated cost to “Systems”.  Each person can be allocated to one or more systems.
• Each System rolls up to one or more Products.  (TABLE B) System costs can be allocated to one or more Products.
• We then want to see the Roll up the cost for each product (Table C)

I need these to be measures (System to Product to COST)  and (Product COST) as we have other “attributes” on our people that we will use to filter, like Role and Location and as such want to see how they affect the rolled up numbers.

My challenge is around the Many to Many relationship of table A and table B.  How can I create a measure to sum the person cost per each system and product.  Table B will find the person cost records for each system and then multiply by the allocation to the product.

 Below is a rough example of how our data is structured.

[mwegener]

Hi @Anonymous ,

You may download my PBIX file from here.
Hope this helps.

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Can't access the pbix file..  @mwegener 

[mwegener]

Hi @Anonymous ,

next try PBIX

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

@mwegener 

same problem, message..  

[mwegener]

Hi @Anonymous ,

The URL probably contained a problematic string

PBIX

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Thanks @mwegener   Appreciate the input/fedback...

I will review some more but ... I'm not sure off the top of my head that this will work.. You created a calculated column not a measure.

In my post I needed to do a "measure" so as to be able to use Attributes about people to "filter" the allocated Person Cost in the rollup to Product Costs... 

Example, if we had a attribute called "role" on Table A for each persons record(s).. (Role 1, Role 2, Role 3, etc.. ) then we would want to see the cost by "ROLE" .. futher we might also have an attribute called "Location"... 

Then the Cost at the Product Level could be broken down by Role and Location.. 

make sense?

[mwegener]

Hi @Anonymous ,

take this...

M Allocate Person Cost = SUMX('Table B', SUMX(RELATEDTABLE('Table A'), 'Table A'[Person Cost] * 'Table B'[Allocat]))

If I answer you question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

This appears to work, thanks a ton for your input/feedback.

.. as a "conclusion" and so as when others find this thread they can learn what is going on "under the covers";

could you "explain" how the nested Sumx functions are "working"..

M Allocate Person Cost = SUMX('Table B', SUMX(RELATEDTABLE('Table A'), 'Table A'[Person Cost] * 'Table B'[Allocat]))

Again thanks for your help!

[mwegener]

Hi @Anonymous ,

has your question been answered?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener · Accepted solution]

Hi @Anonymous 

Here is a little pseudocode as a description:

For each row from [Table B], do the following expression
     For each (related) row of the related [Table A], do the following expression
           Personnel costs from [Table A] * Allocation from [Table B]
     and then calculate the SUM
and then calculate the SUM

At first the allocated person cost of [Table A] ist calculated row by row (SUMX)

and then the SUM for [Table B] ist calculated row by row (SUMX) .

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.