Split the data by criteria

To achieve the desired outcome of splitting sales from warehouses to specific stores based on weight percentages and brand criteria, you can use DAX formulas in Power BI or Excel. Assuming you're working in Power BI, you can follow these steps:

1. Data Modeling: Ensure that you have imported both tables into Power BI, and there should be a relationship established between the main sales table and the split table based on the warehouse ID (WH_ID).

2. Create Calculated Columns:

   • In the split table, calculate the weight for each combination of store, brand, and warehouse.
   • Calculate the total weight for each warehouse.

3. Write DAX Measures:

   • Calculate the weighted sales for each combination of store, brand, and warehouse.
   • Filter out warehouse sales from the total sales.
   • Use the calculated weights to split the sales accordingly.

Here's a sample implementation of the DAX measures:

Weighted Sales =
VAR TotalSales = SUM('Main Sales'[Amount])
RETURN
DIVIDE(
TotalSales * CALCULATE(
SUM(Split[Weight]),
ALLEXCEPT(Split, Split[WH_ID])
),
SUMX(ALL('Main Sales'), 'Main Sales'[Amount])
)

Final Sales =
CALCULATE(
[Weighted Sales],
FILTER(
'Main Sales',
RELATED(Split[SPLIT_ID]) = 'Main Sales'[Store] &&
'Main Sales'[Brand] = Split[Brand]
)
)

Explanation:

• Weighted Sales: This measure calculates the weighted sales for each combination of store, brand, and warehouse by considering the weight percentage from the split table.
• Final Sales: This measure filters out the warehouse sales and calculates the final sales for each store and brand combination based on the weighted sales.

Ensure that you replace 'Main Sales' and Split with your actual table names in your Power BI model. Adjust the column names as per your data model.

This setup should make your solution flexible enough to accommodate changes in weights from month to month, as long as your underlying data and relationships remain intact. Make sure to test the measures with various scenarios to ensure accuracy.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.