Forum Discussion

mayurdhote's avatar
mayurdhote
Regular Visitor
8 years ago
Solved

Issue with Running Total

Let’s consider I have data as shown in below table and I want to calculate Running Total of Amount column as shown in last column i.e Running Total.   Category TypeID Date Amount Ru...
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Hi mayurdhote

     

    In that case.

     

    First Add an Index Column to your Table using QUERY Editor

    (Before adding Index Sort your Table by Dates if not already sorted)

     

     

    Now you can get the Cumulative Total using this Index Column

     

    Running_Total with Index =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( ALL ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
)
v-sihou-msft's avatar
v-sihou-msft
Microsoft Employee
8 years ago

mayurdhote

 

In your scenario, only Data column contains unique values, so you should apply filter on Date column. 

 

Running Total Amount = CALCULATE(SUM(Table1[Amount]),FILTER(ALL(Table1),Table1[Date]<=MAX(Table1[Date])))

 

Regards,

  • mayurdhote's avatar
    mayurdhote
    Regular Visitor
    8 years ago

    Thanks v-sihou-msft !

    But what if in above scenario, date column does not have unique values?

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Hi mayurdhote

       

      In that case.

       

      First Add an Index Column to your Table using QUERY Editor

      (Before adding Index Sort your Table by Dates if not already sorted)

       

       

      Now you can get the Cumulative Total using this Index Column

       

      Running_Total with Index =
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER ( ALL ( Table1 ), Table1[Index] <= MAX ( Table1[Index] ) )
)
      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        mayurdhote

         

        The image below shows the Cumulative Total with and without Index

        It is your sample data. I just added one row with same date