Total of Measures - Referring Across AVE COST and QTY

[mattmalone]

Hi,

I have two tables that are related. One is STOCK_LOC_INFO which has quantities of products at different locations. The second is STOCK_ITEMS, which is the Stock Information table that contains pricing, description etc. etc.

In order for me to compare Sales Orders to Inventory (Stock Turns for those playing at home), I need to get the AVE_COST from STOCK_ITEMS and multiply that by QTY in STOCK_LOC_INFO.

I've written a measure that when I look at the row calculation result, is correct. Summarised tables, though, are flipping out. So I've tried rewriting the DAX to get a more appropriate totals line, but I'm still missing something.

First Measure was: 

SUMSTOCKAVE = SUM(STOCK_ITEMS[AVECOST]) * sum(STOCK_LOC_INFO[QTY])

That would, when I'd put it in a matrix table, show the correct values. In stock QTY of 5, multiplied by AVE COST of $875 would calculate on that line to $4,377.

In the summary table that breaks it down by category and sales outlet, 12 Units at $688 AVE Cost is coming to the total of $888,827 when I have that product's supplier being used in the highlight.

So I thought I'd try to counteract by putting filters of "do not show any with 0 QTY" but that hasn't worked either - which would not have been helpful anyway as you can sell a product without having the stock in.

So - any advice?

[mattmalone]

Hi,

Just an update on this issue - so I've gone to a line by line analysis and I've noticed that there is obviously an error with the formula used and/or the references. There is also a One-To-Many relationship that I made sure was active and correct. Still no luck with understanding what I've done wrong.Incorrect DAX - To get a valuation of QTY in STOCK_LOC_INFO multiplied by LATESTCOST in STOCK_ITEMS

[mattmalone]

If it helps, when I put a STOCKCODE filter on the full page to look at what is happening, it appears to be working correctly.

STOCKCODE (Linked between STOCK_ITEM and STOCK_LOC_INFO)	LATESTCOST (per unit) (STOCK_ITEM)	QTY (STOCK_LOC_INFO)	Expected Result (LATEST COST * QTY) (The Measure)
ABC123456	$100	10	$1,000
DEF9874	$200	0	$0
GHI4321	$150	1	$150
TOTAL		11	$1,150

I'm looking to use The Measure to then run analysis comparing stock value on hand at different outlets to sales orders within the specified time so that what I can then do is look at the sales generated from stock holding to determine best sellers against stock.