Forum Discussion
Stock Allocation
Hello,
i have a question and I already tried a lot but It doesnt seem to work.
I have this table created in excel:
I want new columns.
1: If below zero is 1 for one company for a product, then I want to check PER PRODUCT the Over/understock if there is a company that has overstock that could cover the understock from a company (this is what is shown in max overstock). What i want now is to check if the max overstock < Stock needed and if that is the case I want to take stock from other companies as well. And if company B needs all the available stock company D will not get anything. In the new columns I want to show the name of the company(ies) that have overstock to cover the understock of a company.
2. I want a column with the new Over/understock value after allocation.
It should look like this in the end:
ProductCompany NameStockOrdersProductionStock - OrdersOver/UnderstockBelow zero Stock neededAllocation Comp1 Allocation Comp2Allocation Comp3New Over/Understock
| A | Company A | 30 | 43 | 0 | -13 | -13 | 1 | 13 | Company B | 0 | ||
| A | Company B | 20 | 0 | 20 | 0 | 0 | 7 | |||||
| A | Company C | 40 | 20 | 0 | 20 | 20 | 0 | 0 | 20 | |||
| A | Company D | 10 | 20 | 30 | -10 | 20 | 0 | 0 | 20 | |||
| B | Company A | 15 | 15 | 15 | 0 | 0 | 0 | |||||
| B | Company B | 30 | -30 | -30 | 1 | 30 | Company A | Company E | Company C | 0 | ||
| B | Company C | 2 | 6 | -2 | 4 | 0 | 0 | 1 | ||||
| B | Company D | 20 | 80 | 8 | -60 | -52 | 1 | 52 | -52 | |||
| B | Company E | 5 | 7 | 5 | 12 | 0 | 0 | 0 |
Hope thats clear since its a bit hard for me to explain.
Thanks!
best regards,
Marlene
1 Reply
Step 1: Load the Data into Power BI
- Open Power BI Desktop.
- Go to "Home" > "Get Data" > "Excel," and select your Excel file.
- Select the worksheet containing your table and load the data.
Step 2: Create a New Column for Over/Understock
In Power Query Editor, add a custom column that calculates the Over/Understock:
- In Power Query Editor, go to the "Model" tab.
- Click on "New Column."
- Use the following formula for the "New Over/Understock" column:
[Stock] - [Orders]
Step 3: Allocate Stock from Overstock
- To allocate stock from overstock, you can create a new query that calculates the available overstock for each product and company.
- Merge this query with your main table based on the product and allocate the stock as needed.
Step 4: Create a New Column for Allocation
- In Power Query Editor, add a custom column that calculates the allocation based on your criteria. This may require some advanced M code to identify the companies with overstock to cover understock. The logic can be complex, depending on your specific requirements.
Step 5: Load Data into Power BI Model
- Once you've applied all the necessary transformations in Power Query Editor, load the data into the Power BI model.
Step 6: Create Visualizations
- In the Power BI report view, create visualizations based on the transformed data.
The exact M code for calculating allocation based on your criteria and merging with the main table will depend on the specifics of your data and requirements. This is a complex task and might require expertise in Power Query and DAX.
It's essential to have a good understanding of Power BI, Power Query, and DAX to achieve the desired result. You may need to consult with a Power BI expert or consider a custom development solution if your requirements are highly complex.
Remember to regularly refresh your data in Power BI to keep your visualizations up-to-date as your source data changes.
