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   S...
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.

     

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!