Forum Discussion

Lyssillic's avatar
Lyssillic
Helper I
6 years ago

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. 

Need Help

5 Replies

  • ImkeF's avatar
    ImkeF
    Community Champion
    6 years ago

    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.

     

    • Lyssillic's avatar
      Lyssillic
      Helper I
      6 years ago

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

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

    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

      • ImkeF's avatar
        ImkeF
        Community Champion
        6 years ago

        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.