Showing results for 
Search instead for 
Did you mean: 
Frequent Visitor

Total of Measures - Referring Across AVE COST and QTY



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: 




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?

Frequent Visitor



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_ITEMSIncorrect DAX - To get a valuation of QTY in STOCK_LOC_INFO multiplied by LATESTCOST in STOCK_ITEMS


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.


QTY (STOCK_LOC_INFO)Expected Result (LATEST COST * QTY) (The Measure)
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.

Helpful resources

PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors