Forum Discussion

nicklabh's avatar
nicklabh
New Member
3 years ago

Division based on 2 other values from different columns

Hi everyone, I am very new to DAX and the purpose of using Pbi is to model data for our manufacturing production control.   I have collected data for the below: -Time study data ( Machine, Mater...
tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi nicklabh 

easiest approach is to create a new column in both tables which is the concatenation of Machine and Material # columns that would be a primary key i the dimension (lookup table) and will enable the creation of one to many relationship. Then youe measure would simply be

DivisionMeasure =
AVERAGEX ( TimeStudyRawDat, DIVIDE ( [Prices/HR], TimeStudyRawDat[Prices/HR] ) )

  • nicklabh's avatar
    nicklabh
    New Member
    3 years ago

    Hi tamerj1, thank you for your answer.

     

    I have added the concate column and now it is relationship in 1 to many. However the formula is not producing the correct % calculation

     

    • Completed ticket data table exist measure pieces/Hr for each line
    • Timestudyraw data table exist pieces/Hr

     

     

    Timestudyraw data table (64 pieces/hr)

    Completed ticket data table (Average 40.5 pieces/hr)

     

    Correct calcuation is 40.5/64 = 63%

     

     

     

    However, the current calculation is


     

    • tamerj1's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      3 years ago

      nicklabh 

      Can please copy/paste the dax? It is not clear in the screenshot. Why do you need to use LOOKUPVALUE?

      • nicklabh's avatar
        nicklabh
        New Member
        3 years ago

        My apologies, I used the wrong data anyway.

        Please see the following Dax:


        It should be returning 67.5%, but it is returning 77.1%

         

         

        Completed ticket data:

         

        Timestudydata: