How to Calculate Cumulative or Running Totals in Power BI using DAX

How to Calculate Cumulative or Running Totals for Non Date Columns in Power BI using DAX

Scenario:

Suppose we have a sample Data Model as follows..

The Relationships are as follows..

Now, based on the above Data Model, we will calculate the Cumulative or Running Totals for Date based and Non Date based Columns, using below measure [Units Sold].

[Units Sold] = SUM(tbl_Sales[Units_Sold])

1) Cumulative / Running Total based on Date Column:

Cum Total Units (Date Basis) =

VAR vCumTotal = CALCULATE(

            [Units Sold],

            tbl_Calendar[cDate]<=MAX(tbl_Calendar[cDate]),

            ALL(tbl_Calendar[cDate])

        )

RETURN

IF([Units Sold] <> BLANK(), vCumTotal)

Result:

2) Cumulative / Running Total based on Month Name (Non Date Basis):

Cum Total Units (Month Basis) =

VAR vCumTotal =

        CALCULATE(

                [Units Sold],

                tbl_Calendar[Month_No]<=MAX(tbl_Calendar[Month_No]),

                ALLSELECTED(tbl_Calendar[Month_Name])                

                )

RETURN

IF([Units Sold] <> BLANK(), vCumTotal)

Result:

3) Cumulative / Running Total based on Product ID (Non Date Basis):

Cum Total Units (Prod ID Basis) =

VAR vProdID = MAX(tbl_Products[Prod_Id])

VAR vCumTotal = CALCULATE(

            [Units Sold],

            tbl_Products[Prod_Id]<=vProdID,

            ALLSELECTED(tbl_Products[Prod_Name])

        )

RETURN

IF([Units Sold] <> BLANK(), vCumTotal)

Result:

4) Cumulative / Running Total based on Country Name (Country Rank basis on [Units Sold]):

This method is most useful method, in which we first find the Dense Rank of a Country based on the [Units Sold], and then we Calculate the Cumulative Totals.

Cum Total Units (Country Rank Basis) =

VAR vCurCntryRank =

RANKX(ALLSELECTED(tbl_Region[Country_Name]),[Units Sold],,ASC,DENSE)

VAR vCumTotal =

CALCULATE([Units Sold],

    FILTER(ALLSELECTED(tbl_Region[Country_Name]),

        RANKX(ALLSELECTED(tbl_Region[Country_Name]),

    [Units Sold],,ASC,DENSE) <= vCurCntryRank),

        ALL(tbl_Region[Country_Name])

        )

RETURN

IF([Units Sold] <> BLANK(), vCumTotal)

Result:

In Cumulative Totals calculation, we use the Dense Rank in Ascending Order, hence the Total will show Correctly for the selected Countries.

5) Cumulative / Running Total based on Country Name (Country on Or after Basis):

This method may not be the Ideal choice as it purely works based on the Sorting order of the Country Name.

Cum Total Units (Country OnOrAfter Basis) =

VAR vCumTotal =

CALCULATE([Units Sold],

    FILTER(ALLSELECTED(tbl_Region[Country_Name]),

        ISONORAFTER(tbl_Region[Country_Name],

      MAX(tbl_Region[Country_Name]), DESC)

        )

    )

RETURN

IF([Units Sold] <> BLANK(), vCumTotal)

Result:

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

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

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