Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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 |
|
Solved! Go to 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.
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.
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.
Perfect - thank you so much
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
97 | |
75 | |
63 | |
62 |
User | Count |
---|---|
142 | |
104 | |
104 | |
80 | |
66 |