Forum Discussion
Calculation with blanks
Hello,
I am new to Power BI and need some help setting up a measure. I want to calculate MOI (months of inventory)
The result you see in the above picture may vary based on the past number which is selected in a drop down box.
Total year: Sales for this part number over the last 12 months
Monthly usage: Total year divided by 12
On hand: current inventory
Customer order: open customer orders
Purchase order: open purchase orders
MOI: months of inventory
Either of these fields can show a blank as a result of the part number selected.
Currently my fomula shows:
The reason you are getting 1.78 is because it is calculating (168 - 0 +72) from right to left 0 + 72 = 72 then it subtracts 72 from your 168 and you get 96 divided by 54. You can put additional parenthesis to enclose even further the data between 168-0 so it does this calculation before it adds 72 or see below and use calculate or variables.
Example 1MOI=
((CALCULATE(SUM(YourTable[On Hand])) - CALCULATE(SUM(YourTable[Customer Order])) + CALCULATE(SUM(YourTable[Purchase Order]))) / CALCULATE(SUM(YourTable[Monthly Usage]))
Example 2, you can replace VALUES by sum or calculate sum. I just used the one example you provide so used values.MOI =VAR ONH = VALUES(YourTable[On Hand])VAR CSO = VALUES(YourTable[Customer Order])VAR PO = VALUES(YourTable[Purchase Order])VAR MUS = VALUES(YourTable[Monthly Usage])RETURN(ONH-CSO+PO)/MUSIt worked like a charm. Thank you very much for pointing this out and providing a solution😀😀
2 Replies
The reason you are getting 1.78 is because it is calculating (168 - 0 +72) from right to left 0 + 72 = 72 then it subtracts 72 from your 168 and you get 96 divided by 54. You can put additional parenthesis to enclose even further the data between 168-0 so it does this calculation before it adds 72 or see below and use calculate or variables.
Example 1MOI=
((CALCULATE(SUM(YourTable[On Hand])) - CALCULATE(SUM(YourTable[Customer Order])) + CALCULATE(SUM(YourTable[Purchase Order]))) / CALCULATE(SUM(YourTable[Monthly Usage]))
Example 2, you can replace VALUES by sum or calculate sum. I just used the one example you provide so used values.MOI =VAR ONH = VALUES(YourTable[On Hand])VAR CSO = VALUES(YourTable[Customer Order])VAR PO = VALUES(YourTable[Purchase Order])VAR MUS = VALUES(YourTable[Monthly Usage])RETURN(ONH-CSO+PO)/MUSIt worked like a charm. Thank you very much for pointing this out and providing a solution😀😀