Scenario:
Suppose we have a sample Data Model 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:
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:
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:
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.
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:
Be the first to comment