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