Splitting one row into multiple rows to distribute the value

[bhickey]

First post ever!

I am trying to identify the number of units sold by specific groups using UPC's from a bakery.

My data consists of the daily unit volume by UPC, and I am identifying their groups through a relationship using that UPC.

There are a handful of UPC's that contain multiple other items within them. 

For example: UPC 5555 "Peanut Butter Cookies" 30ct - I show 30 units solds as my value. 

- However, there are also assorted cookie UPC's that contain the above peanut butter cookies.

For example: UPC 8888 "Assorted Cookies" 50ct (contains 20 peanut butter cookies)

I am trying to distribute the the 50 units by calculating that there are 20 peanut butter cookies within the assorted cookie item, as well as then bucketing those values into existing groups (peanut butter, choc chip, sugar, raisin).

I'm hoping to somehow take the row that contains the value of the Assorted item UPC and split it into multiple rows with the correct group. Ideally, these rows would be added to the data set, but looking for other suggestions.

Thank you.

[Anonymous]

Hi @bhickey ,

Has your problem been resolved? If so, could you please mark the helpful reply as 'Answered'? This will assist others in the community who may encounter a similar issue. Thank you.

Otherwise, could you please provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Kedar_Pande]

@bhickey 

Ensure that you have a relationship set up between your Volume table and your Sellable UPCs table based on the UPC.

Create a calculated tables

Distributed Cookies = 
UNION(
SELECTCOLUMNS(
FILTER(Volume, Volume[UPC] = "5555"), // Replace with your actual UPCs for unique cookies
"Group", RELATED(Sellable_UPCs[Group]),
"Description", Volume[Description],
"Volume", Volume[Unit Volume] // This value should be 30 for "Peanut Butter Cookies" UPC
),
SELECTCOLUMNS(
FILTER(Volume, Volume[UPC] = "8888"),
"Group", "Peanut Butter Cookies",
"Description", "Peanut Butter Cookies",
"Volume", DIVIDE(Volume[Unit Volume], 50) * 20 // Distributing 20 of the 50 sold
),
// Repeat for other necessary UPCs
// Add more SELECTCOLUMNS for other UPCs of interest
)

Combined Volume = 
UNION(
Volume,
Distributed_Cookies
)

create measure

Total Units Sold = 
SUMX(
Combined Volume, 
Combined Volume[Volume]
)

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

[rajendraongole1]

Hi  - you can approach this with Power BI by using DAX to calculate and allocate proportions of the units based on the content of each assortment.Create a new calculated column in your sales data to distribute the units of the assorted UPCs to the individual components 

you have to set up a separate table that defines the breakdown of each "Assorted" UPC. 

eg: 

create a relationship between the mapping table and the main sales data table using the UPC columns.

Create a new calculated column in your sales data to distribute the units of the assorted UPCs to the individual components.

Distributed Units =
IF (
'Sales Data'[UPC] IN VALUES('Mapping Table'[Assorted UPC]),
'Sales Data'[Units Sold] *
LOOKUPVALUE('Mapping Table'[Component Quantity], 'Mapping Table'[Assorted UPC], 'Sales Data'[UPC]) /
CALCULATE(SUM('Mapping Table'[Component Quantity]), 'Mapping Table'[Assorted UPC] = 'Sales Data'[UPC]),
'Sales Data'[Units Sold]
)

After distributing the units, you can then group and aggregate the data by Component Name (or other relevant grouping fields) to get the total units sold by each specific type (e.g., peanut butter, chocolate chip) across all items, whether they are standalone or part of an assortment.

I hope , this approach will let you effectively manage and analyze sales data that includes both individual and assorted UPCs, helping to accurately reflect each component’s contribution to overall sales.