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

Calculate Running Total Using Measures

I'd like to create a column that calculates the running total of a measure. It's a bit more complicated than that, because it uses two measures: Over and Under. If the previous Over > 0, then add it to the current Over. If the previous Under > 0, then subtract it from the current Over. If that difference is positive, then show that. If it is negative, make the current equal to zero. I don't want the Under amounts to be summed, only the Over.

 

Here's some sample data:

UnderOver
04
010
110
140
04
04
04

 

I want the running total column to be:

RunningTotal
4 = 4 from Over
14 = 4 + 10 from Over
3 = 14 - 11 from Under
0 = from Over
4 = from Over
8 = 4 + 4 from Over
12 = 8 + 4 from Over

 

Table:

UnderOverRunning Over Total - Under
044
01014
1103
1400
044
048
0412

 

Here's what I have so far, but it's not working:

 

RunningTotal = 
    var filterDate = LASTDATE(Sheet1[Date])
    var output = CALCULATE(
        IF (
            [Under] > 0,
            [Over] - [Under],
            [Over]
        ),
        ALL(Sheet1), 
        Sheet1[Date] <= filterDate
    )
   RETURN IF (
      output > 0,
      ouput,
      0
   )

 

 

It's returning:

RunningTotal
4
14
3
0
0
0
1

 

Please help me understand why my measure isn't working, and how to achieve my goal. 

5 REPLIES 5
v-easonf-msft
Community Support
Community Support

Hi , @Lyssillic 

I'm sorry  that I tried for a while but still didn't work out.

The logic is easy to understand but the formula is not easy to write.

Expect others to help you and share it with more people.

 

In addition, I will try to find someone who is familiar with this to take a further look at this.

I will post back if we have any conclusions on this topic.

 

Best Regards,
Community Support Team _ Eason

@v-easonf-msft Thank you!

 

 

Hi @Lyssillic  

agree with @v-easonf-msft :  the logic is understandable, but unfortunately my DAX skills are not good enough to come up with a solution here. Problem is that you need "real" recurion to set back to 0 with regards to the previous value. Problem with the current formula is that the negative running total caused by the 14 "under" will have to be compensated by the following overs before getting positive again. 

With regards on how to convert your existing measure to a calculated column - it would be this:

RunningTotal = 
    var filterDate = LASTDATE(Sheet1[Date])
    var output = CALCULATE(
        SUMX(Sheet1, IF (
            [Under] > 0,
            [Over] - [Under],
            [Over]
        )),
        ALL(Sheet1), 
        Sheet1[Date] <= filterDate
    )
   RETURN IF (
      output > 0,
      output,
      0
   )

But it will just return the same value than your current measure in the column. 
In Power Query one would use List.Generate or List.Accumulate, but if you run them without a Table.Buffer (as this wouldn't work in Direct Query mode) the result will be very slow for larger datasets.

Posting the PQ sample here in case it helps some DAX genius to pick up the logic:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTKBYgNDXSAyMjC0VIrVgUgZggmwpBGKpKEhSAyIjUG0MaqcCVQOjE0wDIXbZ4pVygIkZYZVytAIJGeOkIsFAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Under = _t, Over = _t, #"Running Over Total - Under" = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Under", Int64.Type}, {"Over", Int64.Type}, {"Running Over Total - Under", Int64.Type}, {"Date", type date}}),
    BufferedTable = Table.Buffer(#"Changed Type"),
    RunningTotal = List.Skip(List.Generate( () =>
        [RowResult = 0, CumulativeResult = 0, Counter = 0],
        each [Counter] <= Table.RowCount(BufferedTable),
        each [
            CurrentRow = BufferedTable{[Counter]},
            RowResult = if CurrentRow[Under] > 0
                            then CurrentRow[Over] - CurrentRow[Under]
                            else CurrentRow[Over],
            CumulativeResult = List.Max( { [CumulativeResult] + RowResult, 0 } ),
            Counter = [Counter] + 1
        ],
        each [CumulativeResult]
    )),
    Result = Table.FromColumns(  Table.ToColumns( BufferedTable ) & { RunningTotal }, Table.ColumnNames(BufferedTable) & {"RunningTotal"} )
in
    Result

 

Actually, even without the buffer the complicated List.Generate-logic wouldn't work in Direct Query unfortunately.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

ImkeF
Super User
Super User

Hi

there is usually no use for measures in calculated columns.

That's because calculated columns are calculated/refreshed when you refresh the data model to catch changes in the underlying data. 

Measures on the other hand respond to user interactions with the reports.

So if you want to create a column with DAX it's best practice to do this with column operations only.

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

@ImkeF Okay, could you help me translate this into a calculated column? Also, I'm restricted to only use DirectQuery.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.