Forum Discussion
Running Totals
Hello All,
This is a sample of my data:
Anyone know what I should do to get the running totals for the Users?
Mind you, my actual data is over 43,000 rows.
I tried to upload a sample file but you have to be a SuperUser.
12 Replies
Hi e175429 , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
Hi e175429 , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.
Hello e175429
1. Build a Date table if this doesn't exist. Suppose this Date table is called 'Date'.2. Suppose the table that has data on your screenshot is called 'Fact'. Create a proper one-to-many relationship from 'Date' to your 'Fact'[PaidDate]
You can use measure,RunningTotal:=
CALCULATE(
COUNTROWS('Fact'),
FILTER(
ALL('Date'[Date]),
'Date'[Date] <= MAX('Date'[Date])
),
VALUES('Fact'[User])
)Thanks.
Hey,
Thank you for your response, but I'm still having a issue.
So, I created your measure and got this:
Which is great!
What would I have to do to get a column that shows the total by month per user?
For instance, under Staley, a column next to "running total" would be month "monthly total" where
Sept-24 = 2
Aug-24 = 2
Dec-23 = 5
Oct-23 = 1
Hello e175429,
Could you please try the following approach:
Running Total per User := VAR CurrentDate = MAX ( 'Fact'[PaidDate] ) RETURN CALCULATE ( COUNTROWS ( 'Fact' ), FILTER ( ALLSELECTED ( 'Fact'[PaidDate] ), 'Fact'[PaidDate] <= CurrentDate ) )Hey,
Thank you for your response. I've created the measure but I'm not sure how to graph this.
This is what I get:
Which is not exactly what I am looking for.
In the table, I want the running totals for everyone, and then when I put a filter on, I would like the running total for that one individual.I have already created a date table:
Power BI (DAX):
Running Total =
CALCULATE(
COUNTROWS('Table'),
FILTER(
ALLSELECTED('Table'),
'Table'[User] = MAX('Table'[User]) &&
'Table'[PaidDate] <= MAX('Table'[PaidDate])
)
)
SQL:COUNT(*) OVER (PARTITION BY [User] ORDER BY PaidDate) AS RunningTotal
A very samall portion of my actal data:
Date: User:
7/1/2023 Nervis
7/1/2023 Nervis
7/1/2023 Clay
7/3/2023 Walter
7/3/2023 Leveston
7/3/2023 Leveston
7/3/2023 Nervis
7/5/2023 Clay
7/9/2023 Clay
8/14/2023 Walter
8/14/2023 Leveston
8/14/2023 Nervis
2/27/2024 Clay
2/27/2024 Leveston
2/27/2024 Nervis
2/27/2024 Walter
2/27/2024 Leveston
2/27/2024 Leveston
3/19/2024 Clay
Hi e175429 ,
In addition to what Rupak_bi has said, you might look at using the RUNNINGSUM visual calculation. Here is some additional info:
https://www.youtube.com/watch?v=pqfBH0ZmYkA
