Calculation with blanks

[bassplayer-nld]

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: 

MOI = (sum('Copper Cable NLW'[OH]) - (SUM('Global Order Board'[Order Qty]) + (SUM('PO Shipments'[Quantity Due])))) / (SUM('Copper Cable'[Qty sold])/12)

Now the result is 1.78 which is not correct: 168 [on hand] - 0 [customer order] + 72 [purchase order] / 54 [Monthly usage] = 4.44

Does this have to do with the blanbk values or amI doing something wrong in the calculation?

[Bmejia · Accepted solution]

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 1

MOI=

((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)/MUS

[Bmejia · Accepted solution]

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 1

MOI=

((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)/MUS

[bassplayer-nld · Accepted solution]

It worked like a charm. Thank you very much for pointing this out and providing a solution😀😀