Forum Discussion
Inventory Availability Coverage
- 6 years ago
Sorry I linked to the wrong file. The file is here:
https://1drv.ms/u/s!Ancq8HFZYL_aiJBRfsmli39RRmqCOA?e=5Uo1n3
There is an Inventory table and a YesNo table.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
I took the following approach. Note that this answers if you have stock in any warehouse, not all warehouses as you stated, but your results of 25%/75% seemed to indicate it was any warehouse.
1) I created a simple table to get a Yes/No based on stock:
Stock Status =
SUMMARIZE(
Inventory,
Inventory[Product],
"On Hand",
VAR IsInStock=
CALCULATE(
SUMX(
Inventory,
IF(Inventory[Quantity] > 0, 1, 0)
)
)
RETURN
IF(IsInStock > 0, "Yes","No")
)It returns this table:
Then I related that table to your inventory data as shown:
Then I created a table visual, dropping in the "On Hand" field from the Stock Status table, and the Percent Available measure from the INventory Table, which is the following measure:
Percent Available =
VAR ProductCount =
COUNTROWS(
DISTINCT( Inventory[Product] )
)
VAR TotalProductCount =
COUNTROWS(
ALL( Inventory[Product] )
)
RETURN
DIVIDE(
ProductCount,
TotalProductCount,
0
)
my PBIX file is here if you want to tinker with it.
Hi edhans,
Thanks for the reply.
Yes, the results are for ANY warehouse. I tried this, but I found I have another problem. Obviusly, the example was a simplification, but the reality is that the Inventory table is a History Inventory, so the Products are repeated for every month because it is a "picture" of the inventory at the end of each month. I use a Calendar Table to relate dates.
The Inventory Table, in fact, looks like this:
| Product | Warehouse | Quantity | Report Date |
| A | California | 2 | ene-20 |
| A | Miami | 0 | ene-20 |
| B | California | 0 | ene-20 |
| B | Miami | 0 | ene-20 |
| C | California | 0 | ene-20 |
| C | Miami | 1 | ene-20 |
| D | California | 10 | ene-20 |
| D | Miami | 4 | ene-20 |
| A | California | 0 | dic-19 |
| A | Miami | 0 | dic-19 |
| B | California | 1 | dic-19 |
| B | Miami | 0 | dic-19 |
| C | California | 0 | dic-19 |
| C | Miami | 0 | dic-19 |
| D | California | 5 | dic-19 |
| D | Miami | 9 | dic-19 |
And the results I would like to get are these:
| On Hand | ||
| Date | Yes | No |
| ene-20 | 75% | 25% |
| dic-19 | 50% | 50% |
Thanks!
Nicolas.
- edhans6 years agoCommunity Champion
Well, that would have been very helpful to know up front. My model won't work as the product in the virtual table is no longer unique. I'd have to go back to the drawing board on this one. I'll see if I can play with it tonight.
- JarroVGIT6 years agoResident RockstarMy measure solution would work in your new requirement, doesn't it? Make a matrix visual, put YesNo column as columns, put date as rows and the measure as values? That's why I made a measure solution as well 🙂
- Nick18106 years agoFrequent VisitorHi!
I downloaded your file, but I could not find the calculated measure. Only tables from other examples. Which name is the measure?
Thanks!- JarroVGIT6 years agoResident Rockstar
Sorry I linked to the wrong file. The file is here:
https://1drv.ms/u/s!Ancq8HFZYL_aiJBRfsmli39RRmqCOA?e=5Uo1n3
There is an Inventory table and a YesNo table.
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂