Dynamically calculated measure with related table

[Kenneth_Lovett]

I have two tables "Production" and "Percent_Breakdown" that are related by 'Product', and need to create a dynamic measures that will calculate an estimated production total by sub-product

"Production" table

Product          Production
A                           50
B                           75

"Percent_Breakdown" table

Product          Sub-Product_1      Sub-Product_2
A                          0.02                            0.15
B                          0.20                            0.043

"Production" table with new measures

Product            Production           Measure_1         Measure_2
A                          50                               1.0                           7.5
B                          75                               15.0                        3.225

I tried a simple calc where  Measure_1 = SUM('Production'[Production]) * SUM('Percent_Breakdown'[Sub-Product_1]), but this did not provide the correct result.

Please advise.

[Anonymous · Accepted solution]

Hi

here is what can do:

Add a column in the Production Table using the following
cal_Sub-Product_1 = Production[Production] * SUMX(RELATEDTABLE(Percent_Breakdown), Percent_Breakdown[Sub-Product_1])
cal_Sub-Product_2 = Production[Production] * SUMX(RELATEDTABLE(Percent_Breakdown), Percent_Breakdown[Sub-Product_2])

If I answer your question, please mark my post as a solution, this will also help others.
Please give Kudos for support.

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

[Kenneth_Lovett]

This worked. However, I have a follow up question:

Why did this work with a Column, but not a Measure?

thanks

[Anonymous]

Hi

RELATED(Column) follows existing many-to-one relationship(s) from the many sides to the one side and returns the single matching value from the other table. In other words, RELATED can access the one-side from the many-side because there are only one row exists in the related table and if no matching row exists, RELATED will return BLANK.

If you are on the one-side of the relationship and you want to access the many-side, then RELATED can not be used because many rows from the many sides of the relationship might be available for a single row on the one side.

In that case, you can use RELATEDTABLE. It returns a table containing all the rows related to the current one.

Hope this help

Tomas Santandreu Polanco |Principal Business Intelligence Consultant
www.designmind.com

SUMX(RELATEDTABLE(Percent_Breakdown