Incidental wrong stockvalue

[JimmyBos]

Hello all, once again i am struggling with a fault in my PBI file. This time i have a formula that calculates if there is enough of an item in stock. Stock - needed for project = leftover stock

Stockformula (measure) (which is correct):

StockQTY = SELECTEDVALUE(InkoopArtikelenStockqty[StockQty])

Needed for project (gives wrong values for a very small percentage of all parts):

POStuklijst[gbkmut.aantal] (this comes from query)

Leftover stock (Measure):

StockQTY-TotaalBehoefte =

VAR StockQTY = POStuklijst[StockQTY]

var Totaal = SELECTEDVALUE(POStuklijst[gbkmut.aantal])

RETURN StockQTY - Totaal

So the 'needed for project' does not show the correct amount for a very few parts. This results in the 'leftover stock' also giving wrong amounts. Any idea what's going wrong? I hope you wont need it, but the query here below: 

= Sql.Database("db.limex.local", "300", [Query="#(lf)SELECT #(lf)gbkmut.oms25, #(lf)PrProject.ParentProject,#(lf)PrProject.CostCenter,#(lf)PrProject.Status,#(lf)gbkmut.project,#(lf) gbkmut.artcode, #(lf) gbkmut.transsubtype, #(lf) gbkmut.bkstnr_sub AS PONO,#(lf) gbkmut.Reviewed, #(lf) gbkmut.regel, #(lf) (gbkmut.aantal * (CASE WHEN transsubtype IN ('A','J') THEN 1 ELSE -1 END)) AS [gbkmut.aantal], #(lf)gbkmut.Checked, #(lf)#(tab)(CASE WHEN (gbkmut.Checked=1 AND gbkmut.BlockItem=1 AND gbkmut.Reviewed=1) THEN 1 ELSE 0 END) AS Completed,#(lf) Items.description, #(lf) Items.CostPriceStandard, #(lf) gbkmut.warehouse_location, #(lf) gbkmut.afldat, #(lf) (gbkmut.bdr_hfl * (CASE WHEN transsubtype IN ('A','J') THEN 1 ELSE -1 END)) AS [gbkmut.bdr_hfl], #(lf) gbkmut.Unitcode, #(lf) Items.Type#(lf) FROM #(lf) gbkmut #(lf)JOIN #(lf) Items ON gbkmut.artcode = Items.Itemcode #(lf)LEFT JOIN #(lf) humres ON gbkmut.res_id = humres.res_id AND gbkmut.res_id IS NOT NULL AND humres.res_id IS NOT NULL #(lf)JOIN #(lf) PrProject ON gbkmut.project = PrProject.ProjectNr #(lf)WHERE #(lf) (gbkmut.transtype = 'B' AND gbkmut.freefield1 = 'P' AND gbkmut.bud_vers='MRP') #(lf) AND gbkmut.transsubtype IN ('B','H') #(lf) AND gbkmut.reknr = Items.GLAccountDistribution#(lf)-- toegevoegd om dubbele items +- te filteren#(lf) AND (PRProject.Status = 'P' OR PRProject.Status = 'A')#(lf)"])

[rajendraongole1 · Accepted solution]

Hi @JimmyBos can you please replace with below:

var Totaal = SUM(POStuklijst[gbkmut.aantal])

or try this:

var Totaal = MAX(POStuklijst[gbkmut.aantal])

Use CALCULATE to Apply the Right Context Try explicitly filtering the stock calculation

StockQTY-TotaalBehoefte =
VAR StockQTY = SELECTEDVALUE(InkoopArtikelenStockqty[StockQty])
VAR Totaal = CALCULATE(
SUM(POStuklijst[gbkmut.aantal]),
ALL(POStuklijst) -- Remove filters to check total
)
RETURN StockQTY - Totaal

Use CALCULATE( SUM(POStuklijst[gbkmut.aantal]), ALL(POStuklijst) ) to debug filtering.
Check table relationships and use RELATED() if needed.

Ensure gbkmut.aantal is numeric.

Hope this helps.

[JimmyBos]

Hello @rajendraongole1 , you must be one of the most active members on this forum! Glad to see your name again.

So i tried your idea's, below the results:

Both the SUM and MAX idea gave me the same error.

There has been detected a circular in dependency: POStuklijst[Vold. Besteld?], POStuklijst[JA/NEE Genoeg besteld], POStuklijst[Vold. Besteld?}.

Then the complete formula with the calculate. This formula gives all the same answers for each row. Every single row (each item) now has the value '-1.879.578,59'.

gbkmut.aantal is indeed numeric.

So the formula i have right now (changed it a little with your idea):

StockQTY-TotaalBehoefte =
VAR StockQTY = SELECTEDVALUE(InkoopArtikelenStockqty[StockQty])
var Totaal = SELECTEDVALUE(POStuklijst[gbkmut.aantal])
RETURN StockQTY - Totaal

Most results are still correct, some are wrong. Below a screenshot, i have marked wrong and correct.

Any idea why some of the results are wrong? The formula is quite simple right? Left column - middle column = right column.

[JimmyBos]

Hello @rajendraongole1 , Seems the problem has been solved! in your previous post you talked about changing the formula in: var Totaal = SUM(POStuklijst[gbkmut.aantal])

This gave me the following error.

There has been detected a circular in dependency: POStuklijst[Vold. Besteld?], POStuklijst[JA/NEE Genoeg besteld], POStuklijst[Vold. Besteld?}.

After changing some of the other formula's, now the error has disappeared! Thanks again!