Hi
I’ve been trying to create a month on month inventory usage linked to total inventory. Whilst I now have the total for inventory correct, the DAX formula or usage escapes me. The issue is that when the inventory quantity is zero the total still takes any inventory usage and adds that to the total of the usage. So the sum of what I see as the detail is not the total at the bottom.
I attach where I am up to with the model and the data I am currently using. The mockup of what I would like to view is on the tab “Valution_Usage” of excel file “Inventory Example”.
Inventory Example.xlsx (29.1 KB)
Inventory Test Example.pbix (228.0 KB)
Any guidance would be appreciated.
Answer:
This is the usual case of misunderstand how DAX calculates totals; it is not a row-by-row total, but a total over the context:
in the total row, the context is all items (A, B, C & D) so the Qty is 62. Since it is not 0, your measure returns the sum of the three transaction types again over all items, correctly resulting in -110.
To get the totals row-by-row you have to explicitly write that in the code, e.g.
Usage Qty V3 =
SUMX(
VALUES( 'Inventory Master'[Item]),
IF( [Qty]<>0,
CALCULATE(
[Movement Quantity],
'Movement Master'[Transaction Type] in {"Sales", "Works Order Issue", "Works Order Adjustment"}
)
)
)
This will iterate over items and check for each item if the Qty is zero, if it is not then it will add the movement qty for the three types to the total sum.
2 Likes
Hi
Thanks. That worked.
I’m now trying to get the first movement date and last usage date from the movement master file without showing the items with zero usage.
