cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
v-amarsh
Microsoft
Microsoft

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

9 REPLIES 9
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
chadrenstrom
Frequent Visitor

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.

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

HI @v-amarsh,

If my formula not helpful, can you please share a pbix file with some dummy data and expected results to test?

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft 

Thanks for your reply. Please find the attached .pbix file. The table on the RHS is the output from the DAX formula.

SUM function used in summarize will always give sum for a group together rather than cumulative progression sum what is expected. 

Note : My cumulative sum should stop once it reaches the MAX value found in Resource Plan for that particular period. In this dummy data set, max value is seen for period FY20-P10. This can vary.

 

I am unable to attach my .pbix here so sending the link for report. Request you to download .pbix from that link.

 

https://msit.powerbi.com/groups/me/reports/19c5c614-0d1d-49ae-a681-55d54cb16729?ctid=72f988bf-86f1-4... 

Thanks

 

Hi @v-amarsh,

I can't access your link, can you please upload the sample to 'onedrive for business' and share the link here?
Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft 

 

I have uplaoded the file to One drive for business and shared with you. Below is the link.

 

https://microsoft-my.sharepoint.com/:u:/p/v-amarsh/EX-TUXMV6PtHhMeFdxGXe_oBue4ky_qToMD9mrrMign9-Q?e=...

 

Thanks

Hi @v-amarsh,

I test the modified formula and it can output the expected result as you shared, please try it if it meets your requirement:

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'[Assigned],
    "MyExpectedOutputColumn",
    VAR maxMonth =
        CALCULATE (
            MAX ( 'Role Master'[Month] ),
            FILTER ( ALLSELECTED ( 'Role Master' ), [Assigned] <> BLANK () ),
            VALUES ( 'Role Master'[Fiscal Year] ),
            VALUES ( 'Role Master'[GSI Name] ),
            VALUES ( 'Role Master'[GD SI Role] )
        )
    RETURN
        IF (
            RIGHT ( [Month], 2 ) < RIGHT ( maxMonth, 2 ),
            CALCULATE (
                SUM ( 'Role Master'[Assigned] ),
                FILTER (
                    ALLSELECTED ( 'Role Master' ),
                    RIGHT ( [Month], 2 ) <= RIGHT ( EARLIER ( 'Role Master'[Month] ), 2 )
                ),
                VALUES ( 'Role Master'[Fiscal Year] ),
                VALUES ( 'Role Master'[GSI Name] ),
                VALUES ( 'Role Master'[GD SI Role] )
            )
        )
)

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Greg_Deckler
Super User
Super User

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

@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors