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 . Col...
Need Help
v-amarsh's avatar
v-amarsh
Microsoft Employee
6 years ago

Hi Anonymous 

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-41af-91ab-2d7cd011db47 

Thanks

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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