Incorrect Measure Totals

[olivere91]

Hi, I'm having trouble with a measure giving inconsistent results:

Available Inventory =

SUM('items'[Inventory]) - SUM('Sales_Lines'[Quantity on SO])

Below is an exmaple, the top line is correct the bottom line is not. What can I do?

[themistoklis]

Hello @olivere91 ,

Are there any other fields in the table which are not displayed in the image that you sent?

[olivere91]

Hi there, plenty of rows following the same formula. The only other colums not shown here are Item number and Item description. There is also a filter for item type category. 

[themistoklis · Accepted solution]

Try the following formula

Available Inventory = SUMX(VALUES ( 'items'[Item_Number] ), CALCULATE ( SUM ('items'[Inventory])))
-
SUMX(VALUES ( 'items'[Item_Number] ), CALCULATE ( SUM ('Sales_Lines'[Quantity on SO])))

[olivere91]

That didn't work. These are the column headers if that helps.

[themistoklis]

@olivere91 

Change 'items'[Item_Number]  to No .. name of first field.

If you could share the file that would be even better. Mask any sensitive data

[olivere91]

Yeah, I replaced the 'items'[Item_Number]  to No. Sorry I'm very new at this I don't know how to mask. 

[olivere91]

I fixed it! the measure now reads:

Available Inventory = SUMX(VALUES ( 'items'[No] ), CALCULATE ( SUM ('items'[Inventory])))

-

SUMX(VALUES ( 'items'[No] ), CALCULATE ( SUM ('Sales_Lines'[On Open SO])))

Can you explain why my original measure wasn't working every time?

[themistoklis]

Hello @olivere91 ,

An article that explains the issue, is the following:

https://blog.enterprisedna.co/why-your-total-is-incorrect-key-power-bi-concept/

It has to do with how PowerBi read the context especially if you have filters applied.

SUMX is an iterator and it reads data row by row

Values function makes sure that every row displayed in the table will be read