cancel
Showing results for
Did you mean:
Frequent Visitor

## Total of Measures - Referring Across AVE COST and QTY

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.

2 REPLIES 2
Frequent Visitor

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

Frequent Visitor

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.

Announcements