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

     

  • WSCTech's avatar
    WSCTech
    Frequent Visitor
    3 years ago

    Hi Zubair,

    I know this is an old post, but I tried to replicate your solution but it seems I'm getting repeated values in my running total column. See snapshot below: