Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I need to solve how to create measures between tables that have many to many relationships.
I’ll describe the process and data
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.
Solved! Go to 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.
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.
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.
Hi @Anonymous ,
The URL probably contained a problematic string
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
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?
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.
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!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |