topic Re: Make Negative Values Zero at a Rolled Up Level in DAX Commands and Tips

Make Negative Values Zero at a Rolled Up Level

Jerid421 — Fri, 08 Dec 2023 14:33:51 GMT

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?

Re: Make Negative Values Zero at a Rolled Up Level

Jerid421 — Fri, 08 Dec 2023 15:08:07 GMT

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 )

Re: Make Negative Values Zero at a Rolled Up Level

Jerid421 — Wed, 13 Dec 2023 21:22:09 GMT

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

Re: Make Negative Values Zero at a Rolled Up Level

Jerid421 — Thu, 14 Dec 2023 19:48:39 GMT

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,

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

)

Re: Make Negative Values Zero at a Rolled Up Level

Greg_Deckler — Mon, 18 Dec 2023 14:38:15 GMT

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

Re: Make Negative Values Zero at a Rolled Up Level

Jerid421 — Mon, 18 Dec 2023 15:12:59 GMT

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

Re: Make Negative Values Zero at a Rolled Up Level

Greg_Deckler — Mon, 18 Dec 2023 17:16:04 GMT

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

Re: Make Negative Values Zero at a Rolled Up Level

Jerid421 — Mon, 18 Dec 2023 19:55:12 GMT

Greg_Deckler I emailed you the .pbix.

Re: Make Negative Values Zero at a Rolled Up Level

Jerid421 — Mon, 18 Dec 2023 19:56:14 GMT

Greg_Deckler I emailed you the .pbix.

Re: Make Negative Values Zero at a Rolled Up Level

Jerid421 — Wed, 20 Dec 2023 21:52:21 GMT

Greg_Deckler Did you receive the file?

Re: Make Negative Values Zero at a Rolled Up Level

Anonymous — Tue, 02 Jan 2024 07:19:24 GMT

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

Re: Make Negative Values Zero at a Rolled Up Level

Jerid421 — Fri, 12 Jan 2024 17:14:21 GMT

Anonymous I sent you a private message... did you get it?