March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I have a table RK_InventarioHistorico like this:
Product | Warehouse | Quantity |
A | California | 2 |
A | Miami | 0 |
B | California | 0 |
B | Miami | 0 |
C | California | 0 |
C | Miami | 1 |
D | California | 10 |
D | Miami | 4 |
I want to know the percentage of the available items/products if at least I have 1 in stock in all the warehouses.
In this example, it should get this result:
Stock | % Availability |
Yes | 75% |
No | 25% |
This explains like this:
There are 4 products/items:
The total sum of prodcut A is 2, so we have at least 1 unit in stock.
The total sum of prodcut B is 0, so we DONT have at least 1 unit in stock.
The total sum of prodcut C is 1, so we have at least 1 unit in stock.
The total sum of prodcut D is 14, so we have at least 1 unit in stock.
We have 3 products where at least have 1 units in stock vs 4 items in total, so we have 75% of items available.
I need help getting this table with these measures:
Stock | % Availability |
Yes | 75% |
No | 25% |
Thanks.
Nicolas.
Solved! Go to Solution.
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! 🙂
Proud to be a Super User!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi 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.
Proud to be a Super User!
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! 🙂
Proud to be a Super User!
Hi Djerro123,
Based on that file I could manage to solve my report
Many Thanks!!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingFun question! I have created the following calculated table that results in what you want:
Table 2 =
ADDCOLUMNS(DATATABLE("Stock", STRING, {{"Yes"}, {"No"}}), "% Available",
VAR _tmpTable = SUMMARIZE(Inventory, Inventory[Product], "TotalInventory", SUM(Inventory[Quantity]))
VAR _rowsOnStock = COUNTROWS(FILTER(_tmpTable, [TotalInventory] > 0))
VAR _rowsNotOnStock = COUNTROWS(_tmpTable)-_rowsOnStock
VAR _totalProducts = COUNTROWS(_tmpTable)
RETURN
IF([Stock] = "Yes",
DIVIDE(_rowsOnStock, _totalProducts),
DIVIDE(_rowsNotOnStock, _totalProducts)
))
What this does is the following. DATATABLE creates a single column table with Yes and No rows. Then I add a column to it called "% Available". I then create a summarytable in memory of the Inventory table. This results in a table of two columns, one with Products and one with the sum of inventory of that product. Then I count the rows where inventory is 0 and where not. Finally, if the current row is "Yes" I divide rowsOnStock by totalProducts. If "No", I divide rowsNotOnStock by totalProducts.
Result is this:
Let me know if this suits your needs. If you want it to be dynamic (e.g. a table visual), then we need another aproach.
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! 🙂
Proud to be a Super User!
I also created a solution based on measures (and a calculated table with just a Yes No column). PBIX can be found here (ignore other tables, they are for other questions)
https://1drv.ms/u/s!Ancq8HFZYL_aiJA8RNb7RX-OsqnWrg?e=3va5Z1
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! 🙂
Proud to be a Super User!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
86 | |
84 | |
76 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |