Forum Discussion

v-amarsh's avatar
v-amarsh
Microsoft Employee
6 years ago

Cumulative sum

Hi Community,

I am trying to get a running total on a field based on condition on a different column.

Below is my input table along with expected output column (Cumulative Assigned)to produce . Column to run a cumulative sum is "Assigned" and Condition is to run cumulative until "Resource Plan" reaches max value for that period. In this case max value ocurs at FY20-P10.

Fiscal YearGSI NameGD SI RoleMonthResource  PlanAssignedCumulative Assigned
FY20XOffshoreFY20-P08614040
FY20XOnsiteFY20-P08412020
FY20XOffshoreFY20-P09662363
FY20XOnsiteFY20-P09441232
FY20XOffshoreFY20-P1012641104
FY20XOnsiteFY20-P10844375
FY20YOffshoreFY20-P08676060
FY20YOnsiteFY20-P08454040
FY20YOffshoreFY20-P095655115
FY20YOnsiteFY20-P09333272
FY20YOffshoreFY20-P1010076191
FY20YOnsiteFY20-P107854126

I have tried Summarize columns function but result is as not as expected. 

Cumulative Table Summary =
SUMMARIZECOLUMNS
(
'Role Master'[GD SI Role],
'Role Master'[Resource Plan],
'Role Master'[GSI Name],
'Role Master'[Fiscal Year],
'Role Master'[Month],
'Role Master'[Forecast Resource Plan],
'Role Master'[Assigned],
'Role Master'[Date],

"Running Cumulative Assigned",CALCULATE(
SUM('Role Master'[Assigned]),
FILTER('Role Master','Role Master'[Resource Plan]<=MAX('Role Master'[Resource Plan])),
VALUES('Role Master'[Assigned])
)
)

Note ; Offshore and onsite values should not be summed up together. (Offshore-->Offshore and Onsite-->Onsite for each period), gorup by each GSI Name

Thanks

Need Help

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    6 years ago

    I don't see anything where you have a sortable column that would allow you to define "previous". If you had that, you could do something like this:

     

    Column =
  SUMX(
    FILTER(
      'Table',
      [GSI Name] = EARLIER('Table'[GSI Name]) && 
        [GD SI Role] = EARLIER([GD SI Role]) && 
          [Some field] <= EARLIER([Some field])
    ),
    [Plan Assigned]
  )
  • chadrenstrom's avatar
    chadrenstrom
    Advocate I
    6 years ago

    Not an answer, but similar idea to the Pareto chart in Excel.  Unfortunately the Pareto chart doesn't exist in Power BI, but there have been a few blog posts around the internet on how to create them.  The order changes based on the group total, so depending what you need, adding an index column prior to any calculations may not be the solution you need.   Then again, this may be a step further than you're looking for.

    • v-amarsh's avatar
      v-amarsh
      Microsoft Employee
      6 years ago

      Thanks for your reply chandrenstrom. I have tried using DAX version equivalent for Parento chart in Excel by some internet sources. However, the result is giving me scalar value of the total sum of values . 

      Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI v-amarsh,

    SUMMARIZECOLUMNS function will return a table, you can't directly use it in a calculated column. I'd like to suggest you aggregate with the result table(Greg_Deckler's suggestion) or create a new calculated table with your formulas. (notice: please add 'allselected' functions to ignore current category group or formula will only calculate on row contents of current category group)

    Cumulative Table=
SUMMARIZE (
    'Role Master',
    'Role Master'[GD SI Role],
    'Role Master'[Resource Plan],
    'Role Master'[GSI Name],
    'Role Master'[Fiscal Year],
    'Role Master'[Month],
    'Role Master'[Forecast Resource Plan],
    'Role Master'[Assigned],
    'Role Master'[Date],
    "Running Cumulative Assigned", CALCULATE (
        SUM ( 'Role Master'[Assigned] ),
        FILTER (
            ALLSELECTED ( 'Role Master' ),
            'Role Master'[Resource Plan] <= EARLIER ( 'Role Master'[Resource Plan] )
        ),
        VALUES ( 'Role Master'[Assigned] )
    )
)

    Regards,

    Xiaoxin Sheng