Comparing values from different tables

Question

This question is in regards to a manufacturing environment.

I have cycle time production data from this year (2017) and I want to compare my current cycle times per product to the best cycle time for that product from my data from 2015. Each product is produced at various times throughout the year and I want to compare to the best cycle time in 2015.

I have imported my 2017 data and my 2015 data. I want to create a visual on my dashboard to show the efficiency of my 2017 cycle time relative to my best 2015 cycle times.

anonymous · Answer

Hi habaholic,&nbsp;I assume&nbsp;both tables&nbsp;have time or dates&nbsp;for their cycle, if you for example want a line chart with both lines so you can compare.You can make a "date&nbsp; table" and make a relationship between both "Date"columns of your two tables and the newly created date table.&nbsp;After you did this you can make&nbsp;the column&nbsp;"Date"(from the newly created table)&nbsp;the X-axis and then the visualisation should work with data from both tables.&nbsp;Link on how to make a date table:https://www.youtube.com/watch?v=F2V0IKp6hKM&nbsp;Please le me know if this worked for you.&nbsp;Regards,L.Meijdam

habaholic · Answer

Both tables have dates. However what I am interested in is comparing this weeks production cycle time vs the best time for that product in 2015.&nbsp;Example (this data is in 2 different tables. One for 2015 data, one for 2017 data)Date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Product Code&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Cycle Time&nbsp; &nbsp; &nbsp; &nbsp;01/01/2015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; P123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1425/08/2015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; P123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1016/11/2015&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; P123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1204/02/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; P123456&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 8&nbsp; &nbsp; &nbsp;&nbsp;&nbsp;Now I want to compare my 04/02/2017 time to my best time in 2015 (10). So the efficiency would be (8/10)*100=80%.&nbsp;I want to do this for all machines/products for the month and then give me a total d3epartment efficiency based on all machine/product results. I have 45 machines working 24 day, producing many different products per month.&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

anonymous · Answer

Hi habaholic,&nbsp;So you want:*Selected value* / MIN(2015[Cycle Time])&nbsp;(the * 100 is not needed if you set the format to %)&nbsp;of a certain product ?&nbsp;Regards,L.Meijdam

habaholic · Answer

Yes, for specific product. In my excel file I have it as:VLOOKUP=([@Product Code],Table5[#ALL],3,FALSE)&nbsp;I want to do the same thing across 2 tables in Power BI.&nbsp;Sorry if I am making this more difficult than it needs to be. I am new to Power BI.

habaholic · Answer

Sorry. The previously mentioned VLOOKUP is to get the best time from 2015. I than use this value to calculate current efficiency.

Forum Discussion

Comparing values from different tables

9 Replies

Related content

Comparing values between different tables

Date Different return wrong value

Comparing Two Different Datasets for missing values

Different collor compare values

Compare different tables