topic Re: Division based on 2 other values from different columns in DAX Commands and Tips

Division based on 2 other values from different columns

nicklabh — Tue, 25 Apr 2023 15:50:38 GMT

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

Re: Division based on 2 other values from different columns

tamerj1 — Tue, 25 Apr 2023 19:13:53 GMT

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

Re: Division based on 2 other values from different columns

nicklabh — Wed, 26 Apr 2023 17:31:54 GMT

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

Re: Division based on 2 other values from different columns

tamerj1 — Wed, 26 Apr 2023 17:37:01 GMT

nicklabh

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

Re: Division based on 2 other values from different columns

nicklabh — Wed, 26 Apr 2023 17:43:16 GMT

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:

Re: Division based on 2 other values from different columns

tamerj1 — Wed, 26 Apr 2023 19:03:10 GMT

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