Creating a Accumulated Net Inventory on hand qty table

Hey ianburns143,

Looking at your issue, I can see the problem. Your measures are summing ALL transactions across the entire dataset instead of respecting the pivot table's row context (CODE and DATE). The ALL(Transactions) function is removing all filters, which is why you're getting totals across everything.

Here's the alternate approach:

Replace your current measures with these corrected versions:

1. Net Inventory (Fixed):

Net Inventory = SUM(Transactions[FINAL QTY])

2. Accumulated Net Inventory (Fixed):

Accumulated Net Inventory =
CALCULATE(
SUM(Transactions[FINAL QTY]),
FILTER(
ALL(Dates[Date]),
Dates[Date] <= MAX(Dates[Date])
)
)

3. Individual Transaction Types (Fixed):

STOCK =
CALCULATE(
SUM(Transactions[FINAL QTY]),
Transactions[TYPE] = "STOCK"
)

WORK ORDER =
CALCULATE(
SUM(Transactions[FINAL QTY]),
Transactions[TYPE] = "WORK ORDER"
)

SALES ORDER =
CALCULATE(
SUM(Transactions[FINAL QTY]),
Transactions[TYPE] = "SALES ORDER"
)

Why Your Original Measures Weren't Working

1. ALL(Transactions) removes all filters from the Transactions table, including the CODE filter from your pivot table rows
2. SUMX(ALL(Transactions), ...) was calculating the same total for every row
3. The measures weren't respecting the individual item codes (A1, B1, C1, etc.)

Key Changes Made

1. Removed ALL(Transactions) - Now measures respect the pivot table context
2. Used SUM() instead of SUMX() - More efficient for simple aggregations
3. Used CALCULATE() with filters - Properly filters by transaction type while maintaining row context
4. Kept ALL(Dates[Date]) only in accumulation - This is correct for time intelligence

Expected Behavior Now

• Each measure will now calculate only for the specific CODE in each row
• The accumulated measure will sum all transactions for that CODE up to the selected date
• Individual transaction type measures will show only their respective values

Try these corrected measures and your pivot table should now show the proper running totals by item code and date as desired.

Fixed? ✓ Mark it • Share it • Help others!

Best Regards,
Jainesh Poojara | Power BI Developer