Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Nick1810
Frequent Visitor

Inventory Availability Coverage

Hello,

I have a table RK_InventarioHistorico like this:

 

ProductWarehouseQuantity
ACalifornia2
AMiami0
BCalifornia0
BMiami0
CCalifornia0
CMiami1
DCalifornia10
DMiami4

 

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
Yes75%
No25%

 

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
Yes75%
No

25%

 

Thanks.

 

Nicolas.

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

9 REPLIES 9
edhans
Super User
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:

20200127 12_45_09-Untitled - Power BI Desktop.png

Then I related that table to your inventory data as shown:
20200127 12_45_44-Untitled - Power BI Desktop.png

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
    )



20200127 12_47_50-Untitled - Power BI Desktop.png

 

my PBIX file is here if you want to tinker with it.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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:

 

ProductWarehouseQuantityReport Date
ACalifornia2ene-20
AMiami0ene-20
BCalifornia0ene-20
BMiami0ene-20
CCalifornia0ene-20
CMiami1ene-20
DCalifornia10ene-20
DMiami4ene-20
ACalifornia0dic-19
AMiami0dic-19
BCalifornia1dic-19
BMiami0dic-19
CCalifornia0dic-19
CMiami0dic-19
DCalifornia5dic-19
DMiami9dic-19

 

And the results I would like to get are these:

 

 On Hand
DateYesNo
ene-2075%25%
dic-1950%50%

 

Thanks!

 

Nicolas.

 

 

 

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 🙂




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Thanks!

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





Did I answer your question? Mark my post as a solution!

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
JarroVGIT
Resident Rockstar
Resident Rockstar

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 valuesOriginal valuesAdjusted formatting on the column to represent percentagesAdjusted 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! 🙂





Did I answer your question? Mark my post as a solution!

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.