Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |