Forum Discussion
Running Total Struggles
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]
RETURN
CALCULATE(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?
Unfortunately the data is sensitive and I do not know a way to provide a sample set. I'm still very new to this.
Try creating a measure to sum the values in the 'Failure Table'[Failure Number]:
Sum of Failures = SUM(Failure Table[Failure Number])
And then use this measure in the Running total measure.
PS. if you create a sample table visual, you can export the data by clicking on the ellipsis of the visual. You can then open the exported data in Excel and change whatever fields are confidential to other "random" names. You can share this sample dataset by uploading the file to a cloud service (Onedrive, Google Drive, Dropbox...) and share from there.
PaulDBrownThe SUM function doesn't work because the "Failure Number" is actually a string, I will change the name to Failure Code to avoid any confusion. They're in the format YOR123456 and DER123456. So far I've been using COUNT to get a total number of failure codes, then using that for my Running Total measure, but something is going wrong.
The only way I can think of is to have my Count Open - Count Closed inside the running total code, but I cannot work out how to actually do it.
I am thinking something like:Count Open minus Count Closed running total in Date =CALCULATE([Count Open minus Count Closed],FILTER(ALLSELECTED('Calendar'[Period]),ISONORAFTER('Calendar'[Period], MAX('Calendar'[Period]), DESC)))
But that gives these results which is still not a running total.
