Forum Discussion

habaholic's avatar
habaholic
Frequent Visitor
8 years ago

Comparing values from different tables

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...
Need Help
Tutorial Requests
habaholic's avatar
habaholic
Frequent Visitor
8 years ago

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's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    habaholic
    Frequent Visitor
    8 years ago

    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.

    • habaholic's avatar
      habaholic
      Frequent Visitor
      8 years ago

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

    • habaholic's avatar
      habaholic
      Frequent Visitor
      8 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        habaholic
        Frequent Visitor
        8 years ago

        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.