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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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


@ 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!:
Mastering Power BI 2nd Edition

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

 

 


@ 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!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors