cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
FAA
Helper I
Helper I

comparing the measures from different tables (not linked directly)

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?

5 REPLIES 5
RicoZhou
Community Support
Community Support

Hi @FAA 

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. 

FAA
Helper I
Helper I

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

 

FAA_1-1614596710839.png

@VijayP 

RicoZhou
Community Support
Community Support

Hi @FAA 

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.

1.png

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

2.png

Result is as below.

3.png

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])))

 

4.png

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. 

 

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
Super User
Super User

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




Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
MY Blog || My YouTube Channel || Connect with me on Linkedin || My Latest Data Story - Ageing Analysis

Proud to be a Super User!


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!