Hi. I work for a manufacturer and I need to create a lookup table from our production data that designates the main production line based on which line has the largest amount of volume.
I have the base table created. It looks like this.
The only step I have left is to designate a main line.
In this case, line 25 has the largest volume, so it should be designated with a “YES” as the main line and Line 24 should be designated “NO” (or Blank). However, I’ve been working on this for hours and can’t come to a good solution. I’m afraid I can’t see the forest for the trees at this point.
Does anyone have any ideas about how to do this?
How do want this to appear? Do you only want 1 Line to say Yes, and all the others NO? I also see you have several Line 25 values in your Line category? Are the Concat values distinct? If so, then would want to go off this category. Let me know how you would like this to be done and I will work on a solution.
Thanks
Jarrett
First, thank you!
Second, the details.
- Appearance: 1 line needs to say yes. All others can say no, or be blank. It doesn’t matter to me.
- Function: The concatenated values are distinct. However, this needs to iterate over all of the item codes to determine which one has the greatest production volume.
So, let me add a little information. I need this to look at each current row, and determine, does this item have greater production than any other item. If yes, then it is the main line. If no, then it is not.
Was I able to bring enough clarity to the issue to help you move forward?
Here is solution I came up with. All I did was add a Calculated Column. Here is a picture of what it looks like, along with the code you will need.
Cheers
Jarrett
This works for the item with the max production. However, I need this to work for each item code individually.
i.e. 10001-069 is correct because line 25 has greater production than line 24. However, 10001-089 should say Yes because it is the only line that produces that product. Likewise, 10001-129 should have line 25 designated as the main line.
Does this make sense?
Yes, makes sense. This might take a while to figure out. I have an appointment I need to get to, will look into this later this evening.
Thanks
Jarrett
Thank you. I really appreciate the help!
I think I got this sorted. Here’s my solution. It was simpler than I was making it.
Main Line 1 =
VAR MaxVolume = CALCULATE(
MAX( 'Main Line'[Volume] ),
FILTER( 'Main Line', 'Main Line'[Item Code] = EARLIER( 'Main Line'[Item Code] ) )
)
Return
IF(
'Main Line'[Volume] = MaxVolume,
"Yes",
"No"
)
Sincerely, thank you for attempting to find a solution!
Great that you came up with a solution! Could you post a screenshot of your solution from Power BI so that I can match what you have in your solution above.
Thanks
Jarrett
I made an additional modification due to some duplicates, but I’m happy to share my solution!
Main Line 1 =
VAR MaxVolume = CALCULATE(
MAX( 'Main Line'[Volume] ),
FILTER( 'Main Line', 'Main Line'[Item Code] = EARLIER( 'Main Line'[Item Code] ) )
)
VAR MaxRunRate = CALCULATE(
MAX( [Actual Run Rate] ),
FILTER( 'Main Line', 'Main Line'[Item Code] = EARLIER( 'Main Line'[Item Code] ) )
)
Return
IF(
'Main Line'[Volume] = MaxVolume && 'Main Line'[Actual Run Rate] = MaxRunRate,
"Yes",
"No"
)
1 Like
This actually won’t matter for the data set your using, but here is my final answer
1 Like
