I am a newbie in PowerBI an I need help to compare stock Inventory between two (or more - if possible) warehouses.
The problem is this: I need to compare inventory from one (or more) warehouse (REF) with other warehouse/s (COMP) and return an inventory list with all products from REF not found in COMP .
I Have to tables: Warehouse and Inventory.
The Inventory table contains all the information and a product can be seen multiple times on the same warehouse (“artid” and “Product” are the same but “entryid” could be different).
I need a report from where I can select reference warehouse(s) and warehouse(s) to compare with, and the result must contains all columns from “Inventory” except “entryid” (this show the total “Qty” of that “Product” ) and the rows contains just the products from “WarehouseRef” selected warehouse(s) not found in “WarehouseComp” selection.
Till now I managed to compare two warehouses:
So I get this information I need except the total “Qty” from ref warehouse.
I need to calculate the result based on “WarehouseRef” and “WarehouseComp” selections and the result must contain one row for every product with a total of “Qty”.
I marked with red filters and modified the result as should be. Please see also pbix file if needed.
For example: I need a list with just the products from warehouse A where are not In Warehouse B .
In the calculated table “tab_compare” DAX formula I need to relpace “A” and “B” based on selection from WarehouseRef and WarehouseComp . The only extra thing in the table results will be “Qty” columns. If you have other solution , please ignore my logic path.
I remember something similar was asked in the forum a while ago using two slicers on a fact table but i can’t find it right off.
did you do a search in the forum or on youtube?
sorry
Keith
I do know to use matrix to obtain the result you purpose but in this case I need a list that respond to the following example question: " What are the products and Qty from A warehouse that are not found in the B warehouse?"
if you look at the matrix it show product warehouse a, warehouse b warehouse c and total quality for that product line
for example product t has 5 products in warehouse A nothing in b and c
if you look at product x you have 5 (warehouse a), 7 (warehouse b) and 8 (warehouse 8) for grand total of 20)
If you have worked with pivot tables in excel its the same.
I think is no need to create a product dimension. All the info is there but I am not able with my DAX knowledge to solve the problem.
The matrix solution is not optimal ( is about thousands of rows in the real model) in my case because a need just the short list with the products in Ref selection. With this, I know the product I must transfer to a specific warehouse that is not in stock there.
I’ve currently got a few solutions I’m working on in the queue ahead of this one, but I recently worked out a solution to a very similar problem with another member. Please take a look at the following thread and see if this helps get you where you need to go:
If not, let me know and I’ll be glad to tackle this one with you when I’ve completed the other two, or someone else on the forum may be able to help you get this one across the goal line.
Thanks, and definitely keep at it. I’ve always been a strong proponent of the fact that the forum is here to benefit both the people asking the question (in terms of getting the solutions they need) AND the people responding (in terms of providing opportunities for us to build our own skills).
I have logged a lot of solutions over time, but also have my fair share of tries that didn’t quite get there that someone else built on for the ultimate solution. Both are valuable, so keep on trying. You’ll connect on some, and the ones you don’t you’ll learn a lot for having given it a shot. At least that’s been my experience…
If I use a measure it cannot return a table… Maybe if the measure will return a value (ex: TRUE or False) and add it to a visual like a table based on Inventory and one column to be the calculated measure.
The only thing that works is just a calculated table (tab_compare )where I entered the warehouse manually. This is not enough…
If I use a calculated table ( tab_compare_2) the refresh is not dynamic and selected values does not work.
I attach the file with the comments for measure and calculated tables. PBI_test-2.pbix (54.3 KB)
Please tell me if you have an idea on how to solve it. Thank you!
Okay, I think I’ve got a good solution for you. You were actually very close, both in concept and execution. The DAX needed to be tweaked a bit to clear the filters on inventory, eliminate the use of SUMMARIZECOLUMNS, which will not work in this case (and frankly is a function I try to avoid – instead using the SUMMARIZE/ADDCOLUMNS combination, or in this case VALUES when only a single column is involved) and eliminate some unnecessary summarization and filtering, but the general concept of your measure was spot on.
You also totally had the right idea in option #1 above. What I did was from the difference table, calculated an IF statement that returned a 1 if the selected artID was in the difference table and a 0 otherwise. I then used that binary result to filter the table visual, making it fully dynamic.
.