I have a complex DAX Measure calculating the month over month percentage change for New_Users collected from Google Analytics data for a website. My calculated column includes an ISINSCOPE clause for a higher level calculation from a measure for Compound Monthly Growth Rate. My issue is the BI calculated column total is inconsistent with the intended scope of my calculated column. I want the column total to be the average of MoM Percentage Change on the row level. Because my DAX measure is not consildated at a row level Power BI is doing something weird in its attempt to aggregate from my DAX measure. My gut feeling is that I need to split up my variables into separate measures and include something like SUMX, AVERAGEX, or HASONEVALUE in the final measure in order to effect the intended average as the column total. I'm at a loss. I wish there was a comprable DAX function like ISINSCOPE to define the column total calculation
Normalized Total New_Users a month:
(Total New_Users * 30) / Number of Days in Month
Month over Month Percentage Change:
(Current Month New_Users - Last Month New_Users) / Last Month New_Users) * 100
I want want the column total to be the average percentage month over month change for New_Users.
CMGR is Compound Monthly Growth Rate.
I have a relational lookup table LU Date Monthly with the first day of the month datetime data type matched to an explicit string value e.g. 2023-01.
CMGR New_Users Normalized =
VAR Days_In_Last_Month = CALCULATE([Days_In_Month], LASTDATE('LU Date Monthly'[Year_Month_dt]))
VAR Last_Month_New_Users = CALCULATE(SUM('Monthly Traffic'[New_Users]), LASTDATE('LU Date Monthly'[Year_Month_dt]))
VAR Normalized_Last_Month_New_Users = DIVIDE(Last_Month_New_Users * 30, Days_In_Last_Month)
VAR Days_In_First_Month = CALCULATE([Days_In_Month], FIRSTDATE('LU Date Monthly'[Year_Month_dt]))
VAR First_Month_New_Users = CALCULATE(SUM('Monthly Traffic'[New_Users]), FIRSTDATE('LU Date Monthly'[Year_Month_dt]))
VAR Normalized_First_Month_New_Users = DIVIDE(First_Month_New_Users * 30, Days_In_First_Month)
VAR Num_Months = CALCULATE(DISTINCTCOUNT('Monthly Traffic'[Year_Month_Id])) - 1
RETURN
IF(
ISBLANK(Last_Month_New_Users) || Last_Month_New_Users <= 0 || First_Month_New_Users <= 0 || Num_Months <= 0,
BLANK(),
//ROUND(100 * (((Normalized_Last_Month_New_Users / Normalized_First_Month_New_Users) ^ (1.0 / Num_Months)) - 1), 0) & "%"
CONCATENATE(ROUND(100 * (((Normalized_Last_Month_New_Users / Normalized_First_Month_New_Users) ^ (1.0 / Num_Months)) - 1), 0), "%")
)
---------------------------------------------------------------------------
Month over Month New_User Growth =
VAR CurrentMonthNewUsers = SUM('Monthly Traffic'[New_Users])
VAR DaysInCurrentMonth = [Days_In_Month]
VAR Normalized_Current_Month_New_Users = DIVIDE(CurrentMonthNewUsers * 30, DaysInCurrentMonth)
VAR PreviousMonthNewUsers =
CALCULATE(
SUM('Monthly Traffic'[New_Users]),
PREVIOUSMONTH('LU Date Monthly'[Year_Month_dt])
)
VAR DaysInPreviousMonth =
CALCULATE(
[Days_In_Month],
PREVIOUSMONTH('LU Date Monthly'[Year_Month_dt])
)
VAR Normalized_Previous_Month_New_Users = DIVIDE(PreviousMonthNewUsers * 30, DaysInPreviousMonth)
VAR Percentage_Change =
DIVIDE(
(Normalized_Current_Month_New_Users - Normalized_Previous_Month_New_Users),
Normalized_Previous_Month_New_Users
)
RETURN
IF(
NOT ISINSCOPE('LU Date Monthly'[Year_Month_dt]),
[CMGR New_Users Normalized],
IF(
ISBLANK(Normalized_Previous_Month_New_Users) || Normalized_Previous_Month_New_Users = 0,
BLANK(),
Percentage_Change
)
)
