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

     



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

      nicklabh 
      In your visual you should be using the [Material #] from the TimeStudyRawDat table (the dimension table). Then apply the following measure. 

      Note: Please do not use LOOKUPVALUE, as it is an inefficient FUNCTION and proves useless in many scenarios.

      Production Efficiency =
AVERAGEX (
    TimeStudyRawDat,
    DIVIDE (
        AVERAGE ( 'Completed Ticket Data'[Pieces/Hr] ),
        TimeStudyRawDat[Prices/HR]
    )
)