Inventory Availability Coverage

[Nick1810]

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.

[edhans]

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.

[Nick1810]

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.

[JarroVGIT]

My 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 🙂

[Nick1810]

Hi!
I downloaded your file, but I could not find the calculated measure. Only tables from other examples. Which name is the measure?

Thanks!

[JarroVGIT · Accepted 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! 🙂

[Nick1810]

Hi Djerro123,

Based on that file I could manage to solve my report

Many Thanks!!

[edhans]

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.

[JarroVGIT]

Fun 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:

Original valuesAdjusted formatting on the column to represent percentages

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! 🙂

[JarroVGIT]

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! 🙂