I have a measure that calculates the average value of inventory for each item in inventory.
Avg Inventory Value = Cost per unit * Avg Inventory balance
The problem is while each individual item is calculating correctly, the grand total should be a sum of Avg Inventory value not the grand total of Cost per unit * grand total of Avg Inventory balance.
Hi @MathewA, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
I spent some time going over that thread and watching the video’s, but I think I’m missing something. I created an example PBX to help.
A couple of notes.
I start with a transaction table, this has all my normal inventory transactions.
I then create a snapshot table that has the inventory balance on hand each day. I do this because I want to get an average of the inventory balances. Maybe there’s an easier way to do it (although my full table does have 28 million rows so this may or may not help with efficiency as well.)
I want to get the average cost of each inventory item on hand over the period, but the total I want to be the sum of all the inventory balances. Here is the code for that.
One other quick point, the items are in a hierarchy. If that matters.
Avg Cost Bal =
VAR AvgCost = AVERAGE('Inventory Summary'[Cost Bal])
VAR TotalAvgCost = SUMMARIZE(Items,Items[Item], "Cost", AvgCost)
RETURN IF(HASONEVALUE(Items[Item]),AvgCost , SUMX(TotalAvgCost, [Cost] ) )
This gets me a number that looks like it might be right, but when you add them all up, it’s wrong.
Thanks,