cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataNoobie
Helper I
Helper I

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

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.

 

DataNoobie_0-1621864729774.png

 

DataNoobie_1-1621864788933.png

 

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. 

 

 

2 ACCEPTED SOLUTIONS

@DataNoobie ,

Sorry, I've misunderstood your requirements.

Here is an option that requires creation of 2 measures:

 

#RT = 
VAR currentID = SELECTEDVALUE ( DummyDataID[ID] )
VAR RTAmt =
    CALCULATE (
        SUMX( DummyDataDemand, [Exceeded Amount]),
        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]),
        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
    )

 

ERD_0-1621940920007.png

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

 

View solution in original post

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

 

 

 

ERD_0-1622046358367.png

 

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

 

View solution in original post

16 REPLIES 16
DataNoobie
Helper I
Helper I

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

DataNoobie
Helper I
Helper I

@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?

 

DataNoobie_0-1622038818228.png

 

DataNoobie_1-1622038931396.png

 

 

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

 

 

 

ERD_0-1622046358367.png

 

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

 

View solution in original post

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

DataNoobie
Helper I
Helper I

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.

 

DataNoobie_0-1622025578999.png

 

DataNoobie
Helper I
Helper I

@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

 

 

 

DataNoobie_0-1621972958624.png

 

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

@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.

@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.:

DataNoobie_0-1622021442802.png

 

@DataNoobie ,

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

ERD_0-1622022083421.pngERD_1-1622022214014.png

 

DataNoobie
Helper I
Helper I

@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?

 

@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.

Thank you - I can't thank you enough!

ERD
Super User
Super User

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

ERD_0-1621926493798.png

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

@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?

 

DataNoobie_0-1621936004799.png

 

 

@DataNoobie ,

Sorry, I've misunderstood your requirements.

Here is an option that requires creation of 2 measures:

 

#RT = 
VAR currentID = SELECTEDVALUE ( DummyDataID[ID] )
VAR RTAmt =
    CALCULATE (
        SUMX( DummyDataDemand, [Exceeded Amount]),
        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]),
        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
    )

 

ERD_0-1621940920007.png

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

 

View solution in original post

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!