Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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
)
)
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
31 | |
14 | |
12 | |
10 | |
10 |