Running Total Struggles

Question

Hi all,&nbsp;I am new to PowerBI and this is the first issue I have not been able to resolve by searching through previous posts.&nbsp;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.&nbsp;Any help would be appreciated.&nbsp;Thank you,&nbsp;Joel

pauldbrown · Answer

JWhitford&nbsp;
If you want the running total for the File Still open &nbsp;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] &lt;= MAX(table[Period])))
2) if you need it by period
running total = CALCULATE(SUM(table[Files Still Open],FILTER(ALL(Table), table[Period] &lt;= 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]RETURN
CALCULATE(SUM(table[Files Still Open],FILTER(ALL(Table), YearPeriod &lt;= MAX(YearPeriod)))
&nbsp;

anonymous · Answer

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.

jwhitford · Answer

PaulDBrown&nbsp;&nbsp;Hi, thanks for the reply.&nbsp;I haven't been able to make any of these commands work.&nbsp;I think the problem I am having is the way I have calculated the tables.&nbsp;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]))&nbsp;'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.&nbsp;The 'Running total' (which doesn't work) column is:Running Total (Failure) = CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;COUNT('Failure Table'[Failure Number]),&nbsp;&nbsp;&nbsp;&nbsp;FILTER(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALLSELECTED('Failure Table'[Financial Period]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ISONORAFTER('Failure Table'[Financial Period], MAX('Failure Table'[Financial Period]), DESC)&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;

pauldbrown · Answer

JWhitford&nbsp;
Any chance you can provide a sample dataset or PBIX file?

jwhitford · Answer

PaulDBrown&nbsp;&nbsp;Unfortunately the data is sensitive and I do not know a way to provide a sample set. I'm still very new to this.

Forum Discussion

Running Total Struggles

Related content

Running Totals

Running total

Waterfall Charts with running totals

Tableau to PowerBI Running Totals Issue

SumX? - cumulative running total