Split the data by criteria

[LUTSENKO_Nikita]

Hi, guys

I need help with right typing of DAX formula to split sales. I have main database with sales from separate stores and from warehouses.
Main issue to split sales from warehouses to specific list of stores.
This is main base, where I have all sales include warehouses, also we have detalization about code of products (column EAN), quantity and amount of sales in cash. 

And I have table with split. WH_ID is code of warehouse (that mentioned in database higher) and SPLIT_ID its codes of stores between them I need to split sales using % of weight from column "%"

And also one more criteria is to check "Brand". One store have 3 weights (for each Brand)

In general, at the end we need to separate sales for each store, like:
1) We have sold products for 1000 euro from one Warehouse that has 3 stores between each of them we need to split this sales
2) 3 stores have weight in 3 products like
First store - first brand - 10%
First store - second brand - 20%
First store - third brand - 15%
Second store - first brand - 5%
Second store - second brand - 5%
Second store - third brand - 5%
Third store - first brand - 10%
Third store - second brand - 10%
Third store - third brand - 20%
In total all 3 stores in all 3 brands have 100% from warehouse
And in final amount we should not have amount of sales from warehouse, only from stores and it will be like:
First store - first brand - 100 euro
First store - second brand - 200 euro
First store - third brand - 150 euro
Second store - first brand - 50 euro
Second store - second brand - 50 euro
Second store - third brand - 50 euro
Third store - first brand - 100 euro
Third store - second brand - 100 euro
Third store - third brand - 200 euro

And it would be great if formula will be flexible to change this weights from month to month
Hope it is real to solve my problem
Thanks in advance

[123abc]

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.