Calculate segment based on material and plant which has high volume ratio.

Question

Hi all,

I have a data set where I have an issue with same material but different plant where Calculated Segmentation should be the same.

Please have a look and correct.

The logic should be that when material number is shared between different plants, calculated segment should be the same.

Segment is determined by a material-plant combination where volume is largest (‘% total’ column).

Segment column is Calculated Segment This Month which is after Plant column.

I tried by using Summarize tool  but no luck. Could anyone help me in this?

Thank you.

book1.xlsx

New Workflow24.yxmd

stj1120 · Answer

Hi @messi007

Thank you for helping me.

But, I think it shouldn't be the logic that I used in Multirow formula. Because, I wrote it as, if the material is same and the segment is different then the current segment record should take the segment from the above record which is wrong.

if an item is shared between several plants, it must appear only for the plant with the biggest volumes.

I think it’s a bit risky to have it several times suggesting a different segment because we can assign only one segment to a 7-item, not several… It should actually calculate by the plant which has highest % total.

Ex : I assign segment A for 23100836 but MRP Planning Category only on 1182, it’s M on 2060.

Please check and help.

Thanks.

messi007 · Answer

@stj1120,

I'm trying to understand your need.

If material is shared we have to take the segment from the biggest Max total plants.

If yes I added Sort based on the % total in order to keep the biggest row first and then use multi row formula grouping by matariel.

I filtered on the duplicated materials to test.

If this in not correct please share the expected the result 🙂

Attached the workflow,

Regards,

Help20210214.yxmd

stj1120 · Answer

Hi @messi007 ,

Thank you for quick responses.

I'd like to acheive the same segments(A or B or D) for the common materials that are shared to different plants but the segment (A or B or D) has to be calculated based on material and plant which has highest % total.

But, it shouldn't be the logic that I used in Multirow formula. Because, I wrote it as, if the same the material is same and the segment is different then the current segment record should take the segment from the above record which is wrong. It should actually calculate by the plant which has highest % total.

Hope it is clear now. Please help.

Thanks.