comparing the measures from different tables (not linked directly)

[Anonymous]

Hi,
So I have one table which has forecast at product-mkt level for different months & year. 
And I have another table where I have constrained forecast at the same level with an additional column of reasons as to why that forecasted sales cant be achieved with standard reasoning in each month-year.
The tables are connected via hierarchy tables (& not directly)

Now I need to find the difference btw the regular forecast and the constrained forecast for each reason. 
When I subtract the SUM measures from those tables and view the difference in graph for different reasons, the data makes no sense. I guess its happening because the tabels are not linked directly.
What is the best way to achieve this?

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Well the tables look like these. And the mapping has been done to the product/mkts hierarchies and to the date table.

@VijayP 

[v-rzhou-msft]

Hi @Anonymous 

Do you want to connect two tables by different tables to make Forecast column and Constrained Forecast Reasonings column in the same table?

You can try merge option in Power Query Editor.

Select Merge  Queries as New in Power Query Editor.

Use Ctrl +Left click to choose colums in two tables as below.

Result is as below.

For Reference: Append vs. Merge in Power BI and Power Query

If you want to build a measure, you can try this way.

Measure = CALCULATE(SUM(Merge1[Forecast]),FILTER(ALL(Table1),Table1[Market Level 1]=MAX(Table2[Market Level 1])&&Table1[Market Level 2]=MAX(Table2[Market Level 2])&&Table1[Product Level 1]=MAX(Table2[Product Level 1])&&Table1[Product Level 2]=MAX(Table2[Product Level 2])&&Table1[Product Level 3]=MAX(Table2[Product Level 3])&&Table1[Year]=MAX(Table2[Year])&&Table1[Month]=MAX(Table2[Month])))

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Hi,

Thanks for this solution. But for this to work, the tables need to have same columns right? My tables have different columns. Only 2 columns are common between them.

[VijayP]

@Anonymous Need to see the modelling diagram and the columns in each table to give better answer