Saturday, December 17, 2022

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

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog