Forum Discussion

csudude's avatar
csudude
Frequent Visitor
6 years ago

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: 


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!

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

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