topic Measure not working for running total with conditions (built on measures) in DAX Commands and Tips

Measure not working for running total with conditions (built on measures)

DataNoobie — Wed, 26 May 2021 17:02:05 GMT

Please can someone help with this measure for a running total. The Power BI report with the model is available.

Example files here.

I'm trying to replicate the calculation in column E from the Excel file. This calculation in Excel caps the lowest value from column D i.e. if it's less than 0, it caps it at 0 so that negative numbers don't accumulate in the running total excessively.

I don't know how to add this condition into my running total measure. This is the measure:

Running Total Incorrect =
VAR Reference = SELECTEDVALUE( 'DummyDataDemand'[ID], 0 )

RETURN
SUMX (
FILTER (
SUMMARIZE ( ALLSELECTED ('DummyDataID'), DummyDataID[ID],
"RunningTotal", [Exceeded Amount] ),
'DummyDataID'[ID] <= Reference ),
[RunningTotal] )

I've also tried to add ADDCOLUMNS to this measure (because summarize used in this way has been depreciated) but I can't get it to work. Help with this would be appreciated.

Re: Measure not working for running total with conditions (built on measures)

ERD — Tue, 25 May 2021 07:08:27 GMT

Hi DataNoobie ,

You can try this measure:

#Running total = VAR currId = SELECTEDVALUE ( DummyDataID[ID] ) VAR _t = ADDCOLUMNS ( SUMMARIZE ( ALLSELECTED ( DummyDataID[ID] ), DummyDataID[ID] ), "@eamt", IF ( [Exceeded Amount] < 0, 0, [Exceeded Amount] ) ) VAR res = SUMX ( FILTER ( _t, [ID] <= currId ), [@eamt] ) RETURN res

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Tue, 25 May 2021 09:49:52 GMT

ERD the measure doesn't give me the desired result e.g. the pattern shown below, in column E. Is it possible to recreate this as a measure?

Re: Measure not working for running total with conditions (built on measures)

ERD — Tue, 25 May 2021 11:09:31 GMT

DataNoobie ,

Sorry, I've misunderstood your requirements.

Here is an option that requires creation of 2 measures:

#RT_filtered = VAR currentID = SELECTEDVALUE ( DummyDataID[ID] ) VAR firstID = MINX ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] ) VAR minValue = MINX ( FILTER ( DummyDataID, DummyDataID[ID] = firstID ), [Exceeded Amount] ) VAR RTAmt = CALCULATE ( SUMX( DummyDataDemand, [Exceeded Amount]), FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID ) ) VAR minOfSum = MIN ( 0, MINX ( FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID ), [#RT] ) ) RETURN IF ( currentID = firstID && minValue < 0, RTAmt - minValue, RTAmt - minOfSum )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Tue, 25 May 2021 13:18:36 GMT

ERD this is amazing - thank you so much for your help.

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Tue, 25 May 2021 13:47:04 GMT

ERD How would I get the highest number from that #RT_Filtered column and a sum of the values from that column in two separate measures?

Re: Measure not working for running total with conditions (built on measures)

ERD — Tue, 25 May 2021 16:17:32 GMT

DataNoobie ,

#RT with sum = IF(HASONEVALUE(DummyDataID[ID]), [#RT_filtered], SUMX(VALUES(DummyDataID[ID]), [#RT_filtered]))

#RT max = MAXX( ADDCOLUMNS( SUMMARIZE(DummyDataID, DummyDataID[ID]), "@RT", [#RT with sum] ), [#RT with sum] )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Tue, 25 May 2021 16:27:38 GMT

Thank you - I can't thank you enough!

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Tue, 25 May 2021 20:05:42 GMT

ERD When I apply the measures into my original Power BI report (with real data) the numbers are too large and I suspect it's because I've got a date table. I've added this into the example model. How would I cater for the date and the other table I use for filtering? Do I need to add in another condtion somewhere i.e. for selected date and selected category (e.g. Filter A)?

I've added a calendar table into the PowerBI model to illustrate the problem. The new file is here.

e.g.

#RT = VAR currentID = SELECTEDVALUE ( DummyDataID[ID] ) VAR RTAmt = CALCULATE ( SUMX( DummyDataDemand, [Exceeded Amount]), FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID // e.g. do I add something here, like: && ALLSELECTED ( Category ) && ALLSELECTED ( Date ) ) ) RETURN RTAmt

Let me know if I should post this as a new question.

Re: Measure not working for running total with conditions (built on measures)

ERD — Wed, 26 May 2021 08:04:13 GMT

DataNoobie ,

In your example the Date column in the DummyDataDemand table was of Text type. In this case Date filter won't work. I assume you need to be able to filter data by date as well. So I've changed it to the correct type (Date).

#RT = VAR currentID = SELECTEDVALUE ( DummyDataID[ID] ) VAR RTAmt = CALCULATE ( SUMX( DummyDataDemand, [Exceeded Amount]) / COUNT(DateTable[Date]), FILTER ( ALLSELECTED ( DummyDataID), DummyDataID[ID] <= currentID ) ) RETURN RTAmt

#RT_filtered = VAR currentID = SELECTEDVALUE ( DummyDataID[ID] ) VAR firstID = MINX ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] ) VAR minValue = MINX ( FILTER ( DummyDataID, DummyDataID[ID] = firstID ), [Exceeded Amount] ) VAR RTAmt = CALCULATE ( SUMX( DummyDataDemand, [Exceeded Amount]) / COUNT(DateTable[Date]), FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID ) ) VAR minOfSum = MIN ( 0, MINX ( FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID ), [#RT] ) ) RETURN IF ( currentID = firstID && minValue < 0, RTAmt - minValue, RTAmt - minOfSum )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Wed, 26 May 2021 09:31:00 GMT

ERD thanks for looking at this. It works when there aren't any date filters applied but not when some dates are selected in the report filter e.g.:

Re: Measure not working for running total with conditions (built on measures)

ERD — Wed, 26 May 2021 09:43:52 GMT

DataNoobie ,

I've used a slicer for dates. But with filters it works the same way:

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Wed, 26 May 2021 10:40:32 GMT

Hi ERD it looks like I have something going on with my real data because it works fine when I filter it for one date but not when other filters are applied. I'm trying to work out what's going on with it.

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Wed, 26 May 2021 14:23:34 GMT

ERD I've added more data to replicate the issue I'm having that I've trying fix today. Here is the file. The running total doesn't work like it did in the first example. Can the measures me modified so that they will work with this example?

Re: Measure not working for running total with conditions (built on measures)

ERD — Wed, 26 May 2021 16:32:25 GMT

DataNoobie ,

We need to change the solution in 2 measures 🙂:

#RT = CALCULATE( SUMX( ADDCOLUMNS( SUMMARIZE( DummyDataID, DummyDataID[ID] ), "ExAmt", [Exceeded Amount] ), [ExAmt] ), FILTER(ALLSELECTED(DummyDataID), DummyDataID[ID] <= MAX(DummyDataDemand[ID])) )

#RT_filtered = VAR currentID = SELECTEDVALUE ( DummyDataID[ID] ) VAR firstID = MINX ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] ) VAR minValue = MINX ( FILTER ( DummyDataID, DummyDataID[ID] = firstID ), [Exceeded Amount] ) VAR minOfSum = MIN ( 0, MINX ( FILTER ( ALLSELECTED ( DummyDataID ), DummyDataID[ID] <= currentID ), [#RT] ) ) RETURN IF ( currentID = firstID && minValue < 0, [#RT] - minValue, [#RT] - minOfSum )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Wed, 26 May 2021 17:00:41 GMT

ERD Thank you. This works perfectly with my data model. 😀

Re: Measure not working for running total with conditions (built on measures)

DataNoobie — Tue, 15 Jun 2021 11:58:39 GMT

ERD,

I have 4 working measures that you created, prefixed with # in this example Power BI report. The measures work fine with the example data but and small samples but when I use real data (approx. 90 million imported rows with filters applied to reduce the dataset for scenario testing) I get out of memory errors on some visuals, unless I apply more filters to reduce the dataset.

Is there a way to re-write these measures so that they will work better with a large dataset?

I've looked optimal dax patterns, in particular this solution from: https://www.sqlbi.com/articles/optimizing-nested-iterators-in-dax/

e.g.

Sales Amount Optimal :=
SUMX (
    VALUES ( Customer[Customer Discount] ),
    SUMX (
        VALUES ( 'Product'[Product Discount] ),
        VAR DiscountedProduct = 1 - 'Product'[Product Discount]
        VAR DiscountedCustomer = 1 - Customer[Customer Discount]
        RETURN
            [Gross Amount]
                * DiscountedProduct
                * DiscountedCustomer
    )
)

The measure I need to get into this pattern (I'm assuming) is below. How do I change it to get into the pattern (the pattern above isn't using a running total)?

#RT =
CALCULATE
SUMX(
ADDCOLUMNS(
SUMMARIZE(
DummyDataID, DummyDataID[ID]
),
"ExAmt",
[Exceeded Amount]
),
[ExAmt]
),
FILTER(ALLSELECTED(DummyDataID), DummyDataID[ID] <= MAX(DummyDataDemand[ID]))
)

Re: Measure not working for running total with conditions (built on measures)

Anonymous — Wed, 22 Jun 2022 12:19:19 GMT

Hello ERD !

Reading your post I though I would have solution I look for DAYS !! But I did try and get blocked...

I got nearly the same issue described in below linked, but adaptating your solution to my issue does not work. If you may have a few minutes to have a look I would be sooooooo gratful !

Cumulative-total-from-Matrix-column

Below is what I have tried - knowwing that Demand is a measure, taking Max (Forecast,OpenOrder), Forecast is a CAlculate from forecast table, OpenOrder is a calculate from Order Book table. Then I cannot do a SumX of Demand as Demand is not in one table.

Demand_Cumulative_Step1 =

VAR currentWeek = SELECTEDVALUE ( DateTable[WeeknYear] )

VAR RTAmt =

CALCULATE (

[Demand],

FILTER ( ALLSELECTED ( DateTable ), DateTable[WeeknYear] <= currentWeek )

)

RETURN RTAmt

Demand_Cumulative_Step2 =

VAR currentID = SELECTEDVALUE ( DateTable[WeeknYear] )

VAR firstID = MINX ( ALLSELECTED ( DateTable ), DateTable[WeeknYear] )

VAR minValue = MINX ( FILTER ( DateTable, DateTable[WeeknYear] = firstID ), [Demand] )

VAR RTAmt =

CALCULATE (

[Demand],

FILTER ( ALLSELECTED ( DateTable ), DateTable[WeeknYear] <= currentID )

)

VAR minOfSum =

MIN (

MINX ( FILTER ( ALLSELECTED ( DateTable ), DateTable[WeeknYear] <= currentID ), [Demand_Cumulative_Step1] )

)

RETURN

IF (

currentID = firstID && minValue < 0,

RTAmt - minValue,

RTAmt - minOfSum

)

Demand Step1 is not starting the good week and taked the max of cumulative forecast vs cumulative order, while I want to simply do cumulative of demand line

Demand Step2 is showing same Demand Step1 and even worse, adding multiple line for FAXTX data whichi is from another table, linked to Product.

Any Idea pelase ?

Thanks you so much !