topic Re: Incorrect Measure Totals in DAX Commands and Tips

Incorrect Measure Totals

olivere91 — Sun, 17 Jan 2021 20:55:40 GMT

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?

Re: Incorrect Measure Totals

themistoklis — Sun, 17 Jan 2021 21:15:13 GMT

Hello olivere91 ,

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

Re: Incorrect Measure Totals

olivere91 — Sun, 17 Jan 2021 21:18:52 GMT

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.

Re: Incorrect Measure Totals

themistoklis — Sun, 17 Jan 2021 21:27:28 GMT

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])))

Re: Incorrect Measure Totals

olivere91 — Sun, 17 Jan 2021 21:50:35 GMT

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

Re: Incorrect Measure Totals

themistoklis — Sun, 17 Jan 2021 21:52:46 GMT

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

Re: Incorrect Measure Totals

olivere91 — Sun, 17 Jan 2021 21:55:49 GMT

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

Re: Incorrect Measure Totals

olivere91 — Sun, 17 Jan 2021 21:58:11 GMT

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?

Re: Incorrect Measure Totals

themistoklis — Sun, 17 Jan 2021 22:12:52 GMT

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