Create buckets based on exact recipe values

[Emmy88_]

Hi all,

I am looking for buckets based on exact same ingredient & volumes. Underneath example data.

You see 2 products: 28682 & 29274. 

28682 has 2 productions -> 100584919  & 100585055, both of them with exact same ingredients and exact same ingredient volume. If this is the case i would like to create 1 bucket for the 2 productions.

29274 has 3 productions, all with different volume & ingredient, so these orders should not be in buckets but separate.

How will i be able to structure this?

Thank you!

Emmy

[v-bofeng-msft]

Hi  @Emmy88_ ,

Could you please clarify what you mean by 'create 1 bucket for the 2 productions'? Do you want to create a new column for bucket?

If possible, could you also provide a sample of your data source or the pbix file?

Best Regards,

Bof

[Emmy88_]

Hi Kedar,

Thank you for your help. I did follow the exact steps however i see bucket for all productions, whereas material 29274 should have separate. Do you know what is going wrong here?

Gr,

Emmy

[Kedar_Pande]

Create a Calculated Column:

UniqueID = 
TableName[Ingredient] & 
"-" & 
FORMAT(TableName[Volume], "General Number")

Create a new table:

ProductionGroups = 
ADDCOLUMNS(
SUMMARIZE(
TableName,
TableName[Product],
TableName[UniqueID]
),
"ProductionCount", COUNTROWS(FILTER(TableName, TableName[UniqueID] = EARLIER(TableName[UniqueID])))
)

Create a measure:

Bucket = 
IF(
MAX(ProductionGroups[ProductionCount]) > 1,
"Bucket",
"Separate"
)

Use a table or matrix visual to display your products.
Add the Bucket measure to your visualization to see bucket assignments

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn