Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I wasn't sure if I should post this in the DAX forum or in the PBI forum. I'm running into an issue calculating a running average that is partitioned by 2 fields.
The measure I am trying to calculate is AvgChurnRate as seen on the far right in the image below:
AvgChurnRate is a running average of the ChurnRate measure. However, the AvgChurnRate should only be the running average of ChurnRate for rows with the same AquiredMonth and AttriterType. AquiredMonth is a column and AttriterType is a measure.
In SQL, the code would look something like this:
,AVG(ChurnRate) OVER (PARTITION BY AcquiredMonth, AttriterType ORDER BY ChurnMonth ASC) AvgChurnRate
This is my best DAX effort:
Earlier should help with this. try below and do any modification needed.
CALCULATE(AVERAGE(Digital_sp_Rpt_CLV_Data[ChurnRate]),
FILTER(ALL(Digital_sp_Rpt_CLV_Data), Digital_sp_Rpt_CLV_Data[ChurnMonth] <= earlier(Digital_sp_Rpt_CLV_Data[ChurnMonth]) &&
Digital_sp_Rpt_CLV_Data[AcquiredMonth] = earlier(Digital_sp_Rpt_CLV_Data[AcquiredMonth]) &&
Digital_sp_Rpt_CLV_Data[AttriterType] = earlier(Digital_sp_Rpt_CLV_Data[AttriterType])))
OR
CALCULATE(AVERAGE(Digital_sp_Rpt_CLV_Data[ChurnRate]),
FILTER((Digital_sp_Rpt_CLV_Data), Digital_sp_Rpt_CLV_Data[ChurnMonth] <= earlier(Digital_sp_Rpt_CLV_Data[ChurnMonth]) &&
Digital_sp_Rpt_CLV_Data[AcquiredMonth] = earlier(Digital_sp_Rpt_CLV_Data[AcquiredMonth]) &&
Digital_sp_Rpt_CLV_Data[AttriterType] = earlier(Digital_sp_Rpt_CLV_Data[AttriterType])))
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on Linkedin
For faster answers, please post data (not a picture of the data)
Here's a measure (I'm not entirely sure it will work since attritertype is a measure but see how you get on)
MeasureTest = VAR _AMonth = MAX(Digital_sp_Rpt_CLV_Data[AcquiredMonth])
VAR _AType = MAX(Digital_sp_Rpt_CLV_Data[AttriterType])
VAR _CMonth = MAX(Digital_sp_Rpt_CLV_Data[ChurnMonth])
RETURN
CALCULATE(AVERAGE(Digital_sp_Rpt_CLV_Data[ChurnRate]),
FILTER(ALL(Digital_sp_Rpt_CLV_Data), Digital_sp_Rpt_CLV_Data[ChurnMonth] <= _CMonth &&
Digital_sp_Rpt_CLV_Data[AcquiredMonth] = _AMonth &&
Digital_sp_Rpt_CLV_Data[AttriterType] = _AType))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |