cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
csudude
Frequent Visitor

Running Avg of Rates by group

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: 
Churn Rate.png

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:

AvgChurnRate =
VAR currAquiredMonth =
    MAX ( Digital_sp_Rpt_CLV_Data[AquiredMonth] )
VAR currChurnMonth =
   MAX ( Digital_sp_Rpt_CLV_Data[ChurnMonth] )
VAR currAttritType =
   MAX (Digital_sp_Rpt_CLV_Data[AttriterTypeColumn])
 
RETURN

AVERAGEX (
        FILTER(
              SUMMARIZE(Digital_sp_Rpt_CLV_Data,
                                  Digital_sp_Rpt_CLV_Data[AquiredMonth],
                                  Digital_sp_Rpt_CLV_Data[ChurnMonth],
                                  Digital_sp_Rpt_CLV_Data[AttriterTypeColumn],
                                  "Churn",[ChurnRate]),
               Digital_sp_Rpt_CLV_Data[AquiredMonth] = currAquiredMonth && Digital_sp_Rpt_CLV_Data[ChurnMonth] <= currChurnMonth && Digital_sp_Rpt_CLV_Data[AttriterTypeColumn] = currAttritType),
[Churn]
)

Obviously the above code doesn't work.  I would be very appreciative of any help I can get.  

Thank you!

2 REPLIES 2
amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

HotChilli
Super User II
Super User II

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

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors