Stock Allocation

[MarlenePirker]

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

[123abc]

Step 1: Load the Data into Power BI

1. Open Power BI Desktop.
2. Go to "Home" > "Get Data" > "Excel," and select your Excel file.
3. Select the worksheet containing your table and load the data.

Step 2: Create a New Column for Over/Understock

1. 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

1. To allocate stock from overstock, you can create a new query that calculates the available overstock for each product and company.
2. 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

1. 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

1. Once you've applied all the necessary transformations in Power Query Editor, load the data into the Power BI model.

Step 6: Create Visualizations

1. 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.