Forum Discussion
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
- ALL(Transactions) removes all filters from the Transactions table, including the CODE filter from your pivot table rows
- SUMX(ALL(Transactions), ...) was calculating the same total for every row
- The measures weren't respecting the individual item codes (A1, B1, C1, etc.)
Key Changes Made
- Removed ALL(Transactions) - Now measures respect the pivot table context
- Used SUM() instead of SUMX() - More efficient for simple aggregations
- Used CALCULATE() with filters - Properly filters by transaction type while maintaining row context
- 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
Thank you so much for taking some time to show me this. I have followed your steps but I am not seeing the desired output. looks like it is summing all the Qty's across the dataset ,
Power Pivot Table below , with settings and what the desired output should look like
Here are the Steps followed
Source Tables
1/ I have alligned all the Fields across all 3 tables to have matching attributes
2/ I have created the date table (date only) running 1st day of my dataset to the last date
3/ In all 3 tables I have grouped all the Qty's having the same dates
Transaction Table
1/ Append all 3 tables together - output below
2/ Created the Calculated Multiplier and Final Qty columns as you requested
transaction table output
Power Pivot
1/Date table and transaction table loaded into Data Model and relationship established between dates
2/ I have created the following measures in the Transaction table
a) Net Inventory:=SUMX(ALL(Transactions),Transactions[QTY] * Transactions[Multiplier])
b) Accumulated Net Inventory:=CALCULATE([Net Inventory],FILTER(ALL(Dates[Date]),Dates[Date] <= MAX(Dates[Date])))
c) STOCK:=SUMX(FILTER(Transactions,Transactions[TYPE] = "STOCK" ),Transactions[FINAL QTY])
d)WORK ORDER:=SUMX(FILTER(Transactions,Transactions[TYPE] = "WORK ORDER" ),Transactions[FINAL QTY])
e)SALES ORDER:=SUMX(FILTER(Transactions,Transactions[TYPE] = "SALES ORDER" ),Transactions[FINAL QTY])
I am probally missing something simple, but if you could advise it would be greatly appreciated
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
- ALL(Transactions) removes all filters from the Transactions table, including the CODE filter from your pivot table rows
- SUMX(ALL(Transactions), ...) was calculating the same total for every row
- The measures weren't respecting the individual item codes (A1, B1, C1, etc.)
Key Changes Made
- Removed ALL(Transactions) - Now measures respect the pivot table context
- Used SUM() instead of SUMX() - More efficient for simple aggregations
- Used CALCULATE() with filters - Properly filters by transaction type while maintaining row context
- 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
Hi jainesh, this is perfect thank you so much for the solution, but more importantly thank you for giving me an insight into the actual coding and why it wasn't working.... very insightful!
Hey ianburns143,
Thank you for the kind recognition - always happy to contribute!Fixed? ✓ Mark it as solution • Give Kuddos • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer