Forum Discussion
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, Material #, Pieces/Hr) "Look up table"
-Completed work ticket data (Machine, Material #, [Measure: Pieces/Hr])
Our material# profiles can run on different machines but has different pieces/hr. Goal is to make a simple calculation by dividing pieces/hr(Completed work ticket data) with pieces/hr (Time study data). Returning production efficiency based on material# and machines.
Currently relationship between the 2 tables are many to many for "material #" and "machine". If i use SUM for the time study data, it will sum all machines producing the same profile. If I use average for the time study data, it will only return an average value for all machines producing the same profile. How could I fix that??
**As you can see, highlighted have same profile but different machine
*Current relationship
5 Replies
- tamerj1
Community Champion
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] ) )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
