Forum Discussion

JWhitford's avatar
JWhitford
Frequent Visitor
5 years ago

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...
Need Help
JWhitford's avatar
JWhitford
Frequent Visitor
5 years ago

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's avatar
PaulDBrown
Community Champion
5 years ago

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's avatar
    JWhitford
    Frequent Visitor
    5 years ago

    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's avatar
      PaulDBrown
      Community Champion
      5 years ago

      JWhitford 

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