Comparing values from different tables

[habaholic]

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]

Hi @habaholic,

I assume both tables have time or dates for their cycle, if you for example want a line chart with both lines so you can compare.

You can make a "date  table" and make a relationship between both "Date"columns of your two tables and the newly created date table.

After you did this you can make the column "Date"(from the newly created table) the X-axis and then the visualisation should work with data from both tables.

Link on how to make a date table:

https://www.youtube.com/watch?v=F2V0IKp6hKM

Please le me know if this worked for you.

Regards,

L.Meijdam

[habaholic]

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.

Example (this data is in 2 different tables. One for 2015 data, one for 2017 data)

Date                                 Product Code                                  Cycle Time       

01/01/2015                      P123456                                          14

25/08/2015                      P123456                                          10

16/11/2015                      P123456                                          12

04/02/2017                      P123456                                          8      

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%.

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.                  

[Anonymous]

Hi @habaholic,

So you want:

*Selected value* / MIN(2015[Cycle Time]) 

(the * 100 is not needed if you set the format to %)

of a certain product ?

Regards,

L.Meijdam

[habaholic]

Yes, for specific product. In my excel file I have it as:

VLOOKUP=([@Product Code],Table5[#ALL],3,FALSE)

I want to do the same thing across 2 tables in Power BI.

Sorry if I am making this more difficult than it needs to be. I am new to Power BI.

[Anonymous]

Hi @habaholic

 I want to do the same thing across 2 tables in Power BI

If I understood it correctly the best cycle time of a product is the lowest cycle time. So if you create a measure for 2015 and 2017 like this:

BestCycleTime2015 = MIN('2015'[Cycle Time]) 

 and

BestCycleTime2017 = MIN('2017'[Cycle Time]) 

and after that you create a measure that divides 2017 with 2015

BestCycleTime2017 divided by BestCycleTime2015 = 
DIVIDE('Table1'[BestCycleTime2017]; '2015'[BestCycleTime2015])

And you set that measure to % format.

After that you can add a slicer where you can select the product you want to compare.

Let me know if this works 🙂

Regards,

L.Meijdam

[habaholic]

OK. One issue, I don't want to compare Best Time 2017 vs Best Time 2015. What I want to do is compare my current production cycle time vs the Best Time 2015 for the same product. If the product was not produced in 2015 then return no value. 

I think your suggestion for the BestCycleTime2015 is OK. But then I need to compare individual production runs from 2017 against this time, and then calculate a total for the month across all products produced.

[Zubair_Muhammad]

@habaholic

Try this measure in Table 2017

=
IF (
    HASONEVALUE ( Table2017[Product] ),
    DIVIDE (
        SUM ( Table2017[Cycle Time] ),
        CALCULATE (
            MAX ( Table2015[Cycle Time] ),
            FILTER ( Table2015, Table2015[Product] = VALUES ( Table2017[Product] ) )
        )
    )
)

[habaholic]

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

[habaholic]

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