Make Negative Values Zero at a Rolled Up Level

[Jerid421]

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?

[v-junyant-msft]

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]

@v-junyant-msft   I sent you a private message... did you get it?

[Jerid421]

@Greg_Deckler  I emailed you the .pbix. 

[Jerid421]

@Greg_Deckler   Did you receive the file?

[Jerid421]

@Greg_Deckler  I can't post it here, but I can share it with you individually.  Should I do that via PM? 

[Greg_Deckler]

@Jerid421 You can email or share it with gdeckler@gmail.com

[Jerid421]

@Greg_Deckler I emailed you the .pbix. 

[Jerid421]

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]

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]

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]

@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