Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Cumulative sum by data and project

    Posted Oct 06, 2019 04:12 PM
      |   view attached
    Hi everybody,

    I was trying for a few days now to calculate a cumulative sum of all entries up on each project from a list.
    Basically i have below table(X) with repeating project names that have values for different periods.
    I would want to have a calculated row that for each line and project adds up previous entries (values), so from previous periods.
    It's similar to a cumulative sum but is per project, and the entries are not necesarily sorted by period.
    I've tried some combinations of Calculate(SUM (X[Value], Filter( All( X[Project], X[Period]<=Earlier(X[Period]) or MAX but no result.

    Project Period Value Cumulative row
    A 201901 0 0
    A 201902 0 0
    A 201903 0 0
    A 201904 1 1
    A 201905 1 2
    A 201906 1 3
    B 201902 1 1
    B 201903 1 2
    B 201904 0 2
    B 201905 1 3
    B 201906 0 3
    C 201901 1 1
    C 201902 1 2
    C 201903 0 0
    C 201904 1 3

    Any ideas on how to make it work?

    ------------------------------
    Adrian Muntean

    ------------------------------

    Attachment(s)

    xlsb
    Workfile.xlsb   19 KB 1 version


  • 2.  RE: Cumulative sum by data and project

    Bronze Contributor
    Posted Oct 07, 2019 02:21 AM
      |   view attached
    Hi Adrian,
    I've attached a spreadsheet of how I would approach the problem. The measure I thought you may require looks like this.
    =CALCULATE( SUM('Input Table'[Value]),
    FILTER(
    ALLEXCEPT('Input Table', 'Input Table'[Project]),
    'Input Table'[Period]<=MAX( 'Input Table'[Period])
    )
    )​


    Steve.

    ------------------------------
    Steven Doherty
    Director
    Market Grunt
    Melbourne VI
    418192168
    ------------------------------

    Attachment(s)



  • 3.  RE: Cumulative sum by data and project

    Posted Oct 07, 2019 04:06 PM
    Hi Steve,
    Many thanks for your support. It works like a charm.
    Now i'll have to see how it behaves to a 200k database.

    Have a good day ahead!

    ------------------------------
    Adrian Muntean
    400439
    ------------------------------



  • 4.  RE: Cumulative sum by data and project

    Bronze Contributor
    Posted Oct 07, 2019 06:43 PM

    Hi Adrian,

    I'm glad it does the trick although, I always forget to ask before offering a solution, "How many rows are in the data table?". A 200K row data table shouldn't be a concern depending on the granularity of the data and the width of the table. From what I can see, the date field appears to be the most granular item, and this shouldn't be a problem.

    If it is an issue I would suggest introducing a Var into the calculation to narrow the table passed to the Filter() function, perhaps something like this;

    Cum Total by Project V2 =
    Var Project_Table =
    Calculatetable(
       Summarize(Input Table,
                 'Look Up Table'[Project ID],
                 'Date Table'[Date],
                 'Input Table'[Value]),
                     Allexcept('Look Up Table',[Project ID]) 
    )
    Return
    
    Calculate(
          Sum('Input Table'[Value]),
              Filter( Project_Table,
                  'Date Table' [Date]<= Max('Date Table'[Date])
    )
    )


    ------------------------------
    Steven Doherty
    Director
    Market Grunt
    Melbourne VI
    0418192168
    ------------------------------