Make Negative Values Zero at a Rolled Up Level

I am trying develop a measure, OpenQuantityRT, to recreate a business process that is currently being done manually in a spreadsheet. The end user sums up quantities ("In-Stock Materials"; 'SupplyDemandItems'[MRPElementOpenQuantity]) by their different statuses (some are ear-marked for assembly, some are already shipped, etc.; 'SupplyDemandItems'[MRPElementCategoryShortName]). They are represented with negative numbers against the Stock-On-Hand number (also a MRPElementCategoryShortName), to reduce it. Sometimes, the number of units earmarked for assembly is greater than the number actually available. This skews the running total (this, and all previous weeks / years) of these quantites, as you can't earmark units that aren't actually available. So, in the spreadsheet, these negative sums are "zeroed" out with an IF() formula after a pivot rolls them up to the Material level. Again, this decision is made after all the plusses and minuses are calculated and the total for each Material SKU is determined to be less than zero. Because this OpenQuantityRT measure is a function of the cost, it also affects the OpenQuantityExtCostRT (Open Quantity Extended Cost Rolling Total) measure, which takes these quantities and multiplies them by their associated price.

Here is my attempt at making these values zero out when they are rolled up to a Material level:

OpenQuantityRT_Test =
VAR __MaterialLevel =
    ISINSCOPE ( SupplyDemandItems[Material] )
VAR __ElementCategoryLevel =
    ISINSCOPE ( SupplyDemandItems[MRPElementCategoryShortName] )
VAR __Qty =
    SUMX (
        SupplyDemandItems,
        IF (
            __MaterialLevel && __ElementCategoryLevel,
            SupplyDemandItems[MRPElementOpenQuantity],
            -- Show negatives at detailed level
            IF (
                SupplyDemandItems[MRPElementOpenQuantity] < 0,
                0,
                -- Show zeros at Material level
                SupplyDemandItems[MRPElementOpenQuantity]
            )
        )
    )
VAR __QtyRT =
    CALCULATE (
        __Qty,
        FILTER (
            ALL ( 'Date Calendar' ),
            'Date Calendar'[YearWeekNum] <= MAX ( 'Date Calendar'[YearWeekNum] )
        )
    )
VAR __Tst1 =
    IF ( __MaterialLevel && __ElementCategoryLevel && __QtyRT < 0, 1, 0 )
VAR __Tst2 =
    IF ( __QtyRT < 0, 1, 0 )
RETURN
    __Tst1

Here is the matrix. I need to get the red box to be zero if the sum of all the rows below it sum to less than zero. If their sum is positive, it just does the regular math. The green boxes should should show the negative totals, so I didn't reference them with the "RT" measures.

Also, this is kind of a two-fold question. I tried to implement this "zero-out" code by using ISINSCOPE() with a little success, but only in a matrix visualization. I am worried that this will not work when I use the measure in a line chart and the Material and MRPElementCategoryShortName are not being used. Is this the right approach?

"DAX help"

"DAX"

11 Replies

Anonymous
Not applicable
2 years ago
Hi Jerid421 ,

Have you solved your problem?
If not, It's hard to see what the problem is just by looking at the DAX. If you are concerned about data security etc, please create a simplified version of the pbix file that contains only the field data referenced by the matrix, the metrics and the matrix visual in the file will be fine. Then post this simplified version of the pbix file to the thread. Thanks!

Best Regards,
Dino Tao
- Jerid421
  Helper II
  2 years ago
  Anonymous I sent you a private message... did you get it?
Jerid421
Helper II
2 years ago
This is the code I am currently working with:

OpenQuantityRT_AllScopes = VAR __IsMaterialLevel = ISINSCOPE('SupplyDemandItems'[Material]) VAR __IsElementCategoryLevel = ISINSCOPE('SupplyDemandItems'[MRPElementCategoryShortName]) VAR __CurrentYearWeek = MAX('Date Calendar'[YearWeekNum]) VAR __RegularRollingTotal = CALCULATE( SUM('SupplyDemandItems'[MRPElementOpenQuantity]), FILTER( ALL('Date Calendar'), 'Date Calendar'[YearWeekNum] <= __CurrentYearWeek ) ) VAR __MaterialLevelandUpRollingTotal = IF( __IsMaterialLevel, CALCULATE( __RegularRollingTotal, ALLEXCEPT('SupplyDemandItems', 'SupplyDemandItems'[Material]) ), BLANK() ) RETURN IF( __IsElementCategoryLevel, __RegularRollingTotal, // Detailed level shows actuals, including negatives MAX(__MaterialLevelandUpRollingTotal, 0) // Material level and up zero out if negative )
- Jerid421
  Helper II
  2 years ago
  I came at it a different way and I think it's better, but now it doesn't seem to be calculating the rolling total correctly (at all):
  
  OpenQuantityRT =
  // Determines if the current context is at the MRPElementCategoryShortName level
  VAR __IsElementCategoryLevel =
  ISINSCOPE('SupplyDemandItems'[MRPElementCategoryShortName])
  
  // Retrieves the maximum YearWeekNum from the Date Calendar table for the current context, to implement the "Running Total"
  VAR __CurrentYearWeek =
  MAX('Date Calendar'[YearWeekNum])
  
  // Corrects the SupplyDemandItems quantities by setting them to zero if their cumulative total is negative
  VAR __CorrectedSupplyDemandItems =
  ADDCOLUMNS(
  'SupplyDemandItems',
  "CorrectedQuantity",
  IF(
  CALCULATE(
  SUM('SupplyDemandItems'[MRPElementOpenQuantity]),
  FILTER(ALL('SupplyDemandItems'), 'SupplyDemandItems'[Material] = EARLIER('SupplyDemandItems'[Material]))
  ) < 0,
  0,
  'SupplyDemandItems'[MRPElementOpenQuantity]
  )
  )
  
  // Calculates the rolling total without negatives for the Material level and up
  VAR __RollingTotalNoNegs =
  CALCULATE(
  SUMX(
  __CorrectedSupplyDemandItems,
  [CorrectedQuantity]
  ),
  FILTER(
  ALL('Date Calendar'),
  'Date Calendar'[YearWeekNum] <= __CurrentYearWeek
  )
  )
  
  // Calculates the regular rolling total for the ElementCategory level
  VAR __RegularRollingTotal =
  CALCULATE(
  SUM('SupplyDemandItems'[MRPElementOpenQuantity]),
  FILTER(
  ALL('Date Calendar'),
  'Date Calendar'[YearWeekNum] <= __CurrentYearWeek
  )
  )
  
  // Returns the appropriate rolling total based on the current context level
  RETURN
  IF(
  __IsElementCategoryLevel,
  __RegularRollingTotal,
  MAX(__RollingTotalNoNegs, 0)
  )
  - Greg_Deckler
    Community Champion
    2 years ago
    Jerid421 Any chance you can share some sample data? Seems like you have a mix of a running total problem as well as measure totals. This may help the most: Matrix Measure Total Triple Threat Rock & Roll - Microsoft Fabric Community

Jerid421

Helper II

2 years ago

My newest code:

OpenQuantityRT_Test3 = 
// Determines if the current context is at the MRPElementCategoryShortName level
VAR __IsElementCategoryLevel =
    ISINSCOPE('SupplyDemandItems'[MRPElementCategoryShortName])

// Retrieves the maximum YearWeekNum from the Date Calendar table for the current context, to implement the "Running Total" 
VAR __CurrentYearWeek =
    MAX('Date Calendar'[YearWeekNum])

// Corrects the SupplyDemandItems quantities by setting them to zero if their cumulative total is negative
VAR __CorrectedSupplyDemandItems =
    ADDCOLUMNS(
        'SupplyDemandItems',
        "CorrectedQuantity", 
        IF(
            CALCULATE(
                SUM('SupplyDemandItems'[MRPElementOpenQuantity]),
                FILTER(ALL('SupplyDemandItems'), 'SupplyDemandItems'[Material] = EARLIER('SupplyDemandItems'[Material]))
            ) < 0,
            0,
            'SupplyDemandItems'[MRPElementOpenQuantity]
        )
    )

// Calculates the rolling total without negatives for the Material level and up
VAR __RollingTotalNoNegs =
    CALCULATE(
        SUMX(
            __CorrectedSupplyDemandItems,
            [CorrectedQuantity]
        ),
        FILTER(
            ALL('Date Calendar'),
            'Date Calendar'[YearWeekNum] <= __CurrentYearWeek
        )
    )

// Calculates the regular rolling total for the ElementCategory level
VAR __RegularRollingTotal =
    CALCULATE(
        SUM('SupplyDemandItems'[MRPElementOpenQuantity]),
        FILTER(
            ALL('Date Calendar'),
            'Date Calendar'[YearWeekNum] <= __CurrentYearWeek
        )
    )

// Returns the appropriate rolling total based on the current context level
RETURN
    IF(
        __IsElementCategoryLevel,
        __RegularRollingTotal,
        MAX(__RollingTotalNoNegs, 0)
    )

Jerid421
Helper II
2 years ago
Greg_Deckler I can't post it here, but I can share it with you individually. Should I do that via PM?
- Greg_Deckler
  Community Champion
  2 years ago
  Jerid421 You can email or share it with [email protected]
  - Jerid421
    Helper II
    2 years ago
    Greg_Deckler I emailed you the .pbix.
Jerid421
Helper II
2 years ago
Greg_Deckler I emailed you the .pbix.
- Jerid421
  Helper II
  2 years ago
  Greg_Deckler Did you receive the file?

Forum Discussion

Make Negative Values Zero at a Rolled Up Level

11 Replies

Related content

IF Negative value

Negative values not be summed correctly

Rolling accumulation

Rolling accumulation

IGNORE NEGATIVE VALUES