Identifying Deadstock and Sending it to another Facility that has Demand. Filter & Xlookup Report

[LT_Maverick]

I have a report that I want to establish in my Power BI Dashboard. But I am not sure how I need to arrange the data. 

I am just not wrapping my head around how this data should be linked so that I can have a slicer to select the facility and the products with a quantity of zero to populate the product numbers and then in the column field populate under the corresponding facility the available stock. 

I want to have another tab where I select a facility and then all products that have stock but equal to or less than 0 demand (returns generate a negative number in our ERP). Then based on the products that populate the facilities with Demand populate their average monthly demand is. 

Knowing these two things will allow facilities to better collaborate and send stock where it can better be utilized. 

What I have been using.

Raw Data file from ERP (Modded to Sample Data)

https://docs.google.com/spreadsheets/d/1NdB9bwinnDk2PJYFRI9_vSfzChRd2MpY/edit?usp=sharing&ouid=10296...

[lbendlin]

Link asks for access, please check.

Note that Power BI is a reporting tool, not a material allocation tool.

[LT_Maverick]

Updating the link. Yes, I want Power BI to help identify the products that should be moved to somewhere else. These reports help drive conversations. Why does this product no longer have demand? Is this product just expiring on the shelf and it gets reordered? Or it's the opposite, I am out of stock and the supplier is telling me it's on backorder. What other sister facility has stock I can borrow from? This is a reporting gap that our ERP is not capable of answering. 

[lbendlin]

What if a product's quantity is 0 at more than one facility?

[LT_Maverick]

That's why I want to utilize a slicer to select an individual facility. Then all the products that have a quantity of zero. Would make up the product list. Then based on the products the other facilities on hand stock would show in the corresponding column. The way I do it today is converting the raw data to a table and just filter the facility that way. Then the filter formula only populates results based on that individual facility. But then to get the quantities of on hand stock for the other facilities I have to have a second sheet that has the raw data unfiltered to populate the results. This spreadsheet ends up being very sluggish and takes forever to update results when switching facilities. Which is why I wanted to move it into Power BI. Its like I need two separate matrix visuals. One that works off the initial slicer and generates the results and then another that works off of the resulted to populate the information needed. This is why I reached out to the community. I just can't figure out how to best configure everything to populate the results I want. It seems simple enough but then its not.  

[lbendlin · Accepted solution]

Something like this?

[LT_Maverick]

I attempted to replicate what you did, but I am not getting all the facilities to populate in the headers like you. Am I doing something wrong? I have duplicated everything to the letter, even double checking the build, visual, properties. So that it all matches your file. Double Checked the relationships as well. 

[lbendlin]

Note that the Facilities table needs to be disconnected.

[LT_Maverick]

I think I got it. 

[v-menakakota]

Hi @LT_Maverick ,

Thank you @lbendlin for providing possible solution.I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-menakakota]

Hi  @LT_Maverick ,

I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please Accept it as a solution and give it a 'Kudos' so others can find it easily.

Thank you.

[v-menakakota]

Hi @LT_Maverick ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you and Regards,
Menaka

[lbendlin]

Note that the Facilities table needs to be disconnected.

[LT_Maverick]

Yes, That's it. So you seperate all the columns into seperate queries, and then created a calculation called show to tell it what products to populate? Just trying to wrap my head around what you did. So I would just change the field and create one more query for the other tab that I want to do, where Average Demand is equal to or less than 0, but the facility has stock. Then to populate the other facilities Avg Demand for that product I reference the AVG Demand Query. Yeah, that’s right! So you broke down all the columns into separate queries and made a calculation called "show" to decide which products to fill in? I'm just trying to get a better grip on what you did. I’d just swap out the field and set up another query for the other tab I want, where the average demand is zero or less, but the facility still has stock. To get the average demand for that product at the other facilities, I’ll just refer to the average demand query. Do I have that right?