Forum Discussion
Running Total Struggles
Hi all,
I am new to PowerBI and this is the first issue I have not been able to resolve by searching through previous posts.
I have a list of fault tickets with opened and closed dates. There is a backlog of open files and I am trying to show a running total of how the backlog has grown. To do this I have used a running total on a chart and this seems to be working fine.
I am trying to present this same information in the form of a table. Below is a screenshot of the results:
A you can see, the Running Total column is just replicating the Files Raised column. I cannot understand why. The correct calculation should reflect the chart, showing a steady increase in numbers until period 7 2020 with a value of 741.
Any help would be appreciated.
Thank you,
Joel
10 Replies
If you want the running total for the File Still open column, Try:
1) if you need it by year
Running Total = CALCULATE(SUM(table[Files Still Open],
FILTER(ALLEXCEPT(Table, Table[Financial Year]), table[Period] <= MAX(table[Period])))2) if you need it by period
running total = CALCULATE(SUM(table[Files Still Open],
FILTER(ALL(Table), table[Period] <= MAX(table[Period])))3) if you need it by over the whole table ascending by year
Running total =
VAR YearPeriod = table[Year] * 100 + table[Period]
RETURNCALCULATE(SUM(table[Files Still Open],
FILTER(ALL(Table), YearPeriod <= MAX(YearPeriod)))Hi, thanks for the reply.
I haven't been able to make any of these commands work.
I think the problem I am having is the way I have calculated the tables.
The Files Raised column is:
Count Open = COUNT('Failure Table'[Failure Number])Failure numbers being a string.
The files closed column is:
Count Closed = CALCULATE(COUNT('Failure Table'[Failure Number]), USERELATIONSHIP('Calendar'[Date], 'Failure Table'[Date of Engineer Sign-Off]))'Files still open' is just a quick measure with "Count Open = COUNT('Failure Table'[Failure Number])" with a filter on for blank sign-off date.
The 'Running total' (which doesn't work) column is:
Running Total (Failure) =CALCULATE(COUNT('Failure Table'[Failure Number]),FILTER(ALLSELECTED('Failure Table'[Financial Period]),ISONORAFTER('Failure Table'[Financial Period], MAX('Failure Table'[Financial Period]), DESC)))Any chance you can provide a sample dataset or PBIX file?
When it comes to running totals, the "Quirky Update" blows the doors off of all other T-SQL methods. In SQL Server 2000, the "Quirky Update" is the only high speed method to do the equivalent of "partitioned" ROW_NUMBERs and RANK without the slothfulness of some explicit RBAR loop or a "Triangular Join". The "Quirky Update" does in about 6 seconds that which takes a cursor almost 8 minutes to accomplish. To put things into perspective, that means the "Quirky Update" is about 80 times faster than a cursor when done "in place" in the same table.
