cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## 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 5
Super User

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

New Member

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

Super User

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

New Member

My apologies, I used the wrong data anyway.

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

Completed ticket data:

Timestudydata:

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors