cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## 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:

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?
2 ACCEPTED SOLUTIONS
Super User

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

Regular Visitor

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

2 REPLIES 2
Super User

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

Regular Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

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

#### Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors