Supply chain measure - In Stock Percentage

[MrKwanorama]

For demand planning, I am looking to create an overview of a company in-stock-percentage or ISP.

Challenge is, that I have the same partnumbers on multiple locations and need the ISP as one consolidated percentage of Category regardless of location.

• How do I create a measure to return the percentage of Category holding Inventory of Partnumber in any Plant?
• For reference, Drillsystem ISP in below table is 75% since 6 of 8 partnumbers are in stock in any given Plant

Country	Category	Partnumber	Plant	Inventory
ES	Drillsystem	part1	warehouse1	2
ES	Drillsystem	part1	warehouse2	0
ES	Drillsystem	part2	warehouse1	0
ES	Drillsystem	part2	warehouse2	5
ES	Drillsystem	part3	warehouse1	0
ES	Drillsystem	part3	warehouse2	0
ES	Drillsystem	part4	warehouse1	0
ES	Drillsystem	part4	warehouse2	4
ES	Drillsystem	part5	warehouse1	5
ES	Drillsystem	part5	warehouse2	2
ES	Drillsystem	part6	warehouse1	0
ES	Drillsystem	part6	warehouse2	0
ES	Drillsystem	part7	warehouse1	5
ES	Drillsystem	part7	warehouse2	2
ES	Drillsystem	part8	warehouse1	0
ES	Drillsystem	part8	warehouse2	5

[v-eqin-msft · Accepted solution]

Hi @MrKwanorama ,

According to my understanding, you want to calculate the ratio of distinct PartNumber when Inventory<>0 of each Country and each Category, right?

If so , please try:

Drillsysytem ISP = 
var _partNo= CALCULATE(DISTINCTCOUNT('Table'[Partnumber]), ALLEXCEPT('Table','Table'[Country],'Table'[Category]))
var _inveNo= CALCULATE(DISTINCTCOUNT('Table'[Partnumber]),FILTER(ALL('Table'),[Country]=MAX('Table'[Country]) && [Category]=MAX('Table'[Category]) && [Inventory]<>0))
return DIVIDE(_inveNo,_partNo)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[MrKwanorama]

Oh - was too fast there. What I need is to calculate the ratio of instock for the category. E.g. if we have inventory on 7 of 10 partnumbers in any plant, the ISP is 70% for the category.

[MrKwanorama]

Perfect - thank you so much

[MrKwanorama]