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

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

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