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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

17 REPLIES 17
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.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

@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

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

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

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Paimath
Frequent Visitor

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 (
0,
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.
 
Paimath_1-1655900352100.png

 

 
Any Idea pelase ?
 
Thanks you so much !

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors