cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mattmalone
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.

 

So - any advice?

2 REPLIES 2
mattmalone
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_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.

 

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$10010$1,000
DEF9874$2000$0
GHI4321$1501$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.

Helpful resources

Announcements
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

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors