Running Total Struggles

[JWhitford]

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

[Anonymous]

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.

[PaulDBrown]

@JWhitford 

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

[JWhitford]

@PaulDBrown 

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)

    )

)

[PaulDBrown]

@JWhitford 

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.

[JWhitford]

@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.

[PaulDBrown]

@JWhitford 

Try this.

1) First create a calculated column to establish the YearPeriod for your table:

YearPeriod Column = Table[Year] * 100 + Table[Period]

2) Next create a dimension table for your YearPeriod/Year/Period

 3) Join the Dim YearPeriod Table to your fact table in a one-to-many relationship

5) Create a measure which is the sum of the column you have created for Raised Files:

Sum of Files Raised = SUM(Table1[Files Raised])

6) Create the running totals measures you need:

Running total YearPeriod = 
CALCULATE([Sum of Files Raised], 
               FILTER(ALL ('Dim YearPeriod'),  
                   'Dim YearPeriod'[YearPeriod] <= MAX('Dim YearPeriod'[YearPeriod])))

Running total by period = 
CALCULATE([Sum of Files Raised], 
            FILTER(ALL ('Dim YearPeriod'),  
                 'Dim YearPeriod'[Period] <= MAX('Dim YearPeriod'[Period])))

Running total by Year = 
CALCULATE([Sum of Files Raised], 
                  FILTER(ALL('Dim YearPeriod'), 
                        'Dim YearPeriod'[Year] <= MAX('Dim YearPeriod'[Year])))

And this is the result:

[JWhitford]

@PaulDBrown 

Hi, Thank you for all your help, and sorry for my slow reply. I had to step away from the computer for a couple of days. I have not been able to follow your instructions due to errors at every stage. I think it is because of the way the data is presented. I have cleaned up and anonymised the data as far as I can. How do I upload the file to share with you? 

Thanks,

Joel

[PaulDBrown]

@JWhitford 

You can upload a file to a cloud service (Onedrive, Google Drive, Dropbox...) and share from there.

[PaulDBrown]

@JWhitford 

Any chance you can provide a sample dataset or PBIX file?

[JWhitford]

@PaulDBrown 

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