Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Jerid421
Helper II
Helper II

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. 

Jerid421_0-1701974414561.png

 

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?

11 REPLIES 11
v-junyant-msft
Community Support
Community Support

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

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

Jerid421
Helper II
Helper II

@Greg_Deckler  I emailed you the .pbix. 

@Greg_Deckler   Did you receive the file?

Jerid421
Helper II
Helper II

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

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

@Greg_Deckler I emailed you the .pbix. 

Jerid421
Helper II
Helper II

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
Helper II

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
    )

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)
    )

@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

 

 



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

Top Solution Authors