Forum Discussion

JimmyBos's avatar
JimmyBos
Helper II
1 year ago
Solved

Incidental wrong stockvalue

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)"])
Need Help
Show and Tell
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    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.

     

3 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    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's avatar
      JimmyBos
      Helper II
      1 year ago

      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's avatar
        JimmyBos
        Helper II
        1 year ago

        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!