cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mayurdhote
Regular Visitor

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

Running Total

A

12

19-11-2017

10

10

A

23

20-11-2017

10

20

B

15

21-11-2017

10

30

C

12

23-11-2017

10

40

B

23

24-11-2017

10

50

C

16

25-11-2017

10

60

 

 

I have tried with built in functionality of power BI i.e. “New quick measure->Running Total”. But it does not  work for me as you can see here multiple dimension present in the table (Category, TypeID, Date).

 

I have also tried with the solution from the following post, but it does not work for me:

http://community.powerbi.com/t5/Desktop/DAX-Running-total-by-another-column/td-p/10512

 

Any ideas would be much appreciated.  Thanks for any help in advance!

1 ACCEPTED SOLUTION

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)

 

47.png

 

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

Please try my custom visuals


View solution in original post

4 REPLIES 4
v-sihou-msft
Microsoft
Microsoft

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

24.PNG

 

Regards,

Thanks @v-sihou-msft !

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

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)

 

47.png

 

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

Please try my custom visuals


View solution in original post

@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

 

49.png

Regards
Zubair

Please try my custom visuals


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors