Moving average of a percentage Measure

[Preetish_1]

Hi guys,

I understood how a moving average can be created by reading up on the forums.

But I need a moving average of a percentage measure which I have created. The percentage measure basically calculates the tickets that were solved on time against the total tickets in the month, to give us a 'Success Measure'.

I need a 3/6 month moving average of this 'Success Measure'.

I currently use this formula, but this only gives me the average (same as the Success Measure )

M_Moving Average = CALCULATE(
     DIVIDE([M_PassedTickets]; CALCULATE([M_PassedTickets] + [M_FailedTickets]);0);
      DATESINPERIOD(T1[C_FullDate]; LASTDATE(T1[C_FullDate]);-3;MONTH)
       )

Any help would be greatly appreciated. I have been struggling with this for a while.
Thank you.

Preetish

[vanessafvg]

@Preetish_1

what result are you getting and what are you expecting?  can you illustrate by providing examples of the figures

[Preetish_1]

Hi Vanessa,

Thanks for your reply. I hope I am able to describe it better below.

In the visual attached in my question, the light blue part of the column is giving a count of the Passed tickets and the dark ones a count of the Failed tickets.
I am able to calculate the percentage of the passed tickets, over the total count of tickets for each month using a combination of measures. This is the Percentage I have for each month : 91, 80 and 78 ( starting 2017 May and going backwards).
I am trying to achieve a 3 month moving average of the percentage success rate ((91+80+78)/3) to display as a line in the same visual. (Not just for the last 3 months, but across the entire visual). But the formula I used is just giving me the percentage success rate, and not a moving average of the success rate.

Thanks,

Preetish

[vanessafvg]

@Preetish_1

will this previous post help at all?

https://community.powerbi.com/t5/Developer/How-to-calculate-Moving-Average-based-on-a-Rolling-10-hou...

[Preetish_1]

Hi @vanessafvg,

That was a good post and I think I now understand the logic behind the working of Aggregation Patterns.

But I still have a problem with getting the right results.

I tried to the normal moving averages of the time period and yet, I have the same problem.

I use the following formulae.

M_Moving AVG = CALCULATE(
     AVERAGEX(T1; T1[C_Hrs]);;
      DATESINPERIOD(T1[C_FullDate]; LASTDATE(T1[C_FullDate]);-3;MONTH)
       )

M_Moving Average = CALCULATE(
    AVERAGEX(T1; T1[C_Hrs]);
     FILTER(
      ALL(T1[C_FullDate]);
       T1[C_FullDate] >= MAX(T1)-2
       && T1[C_FullDate] <= MAX( T1[C_FullDate])
       ))

But the results I get are normal averages and not Moving averages. And its matching with the normal average which Power Bi summarizes on its own. (as in the attached picture)

Thanks,
Preetish

[vanessafvg]

@Preetish_1 have you tried the quick measures?

there is a rolling average there, have you tried that?

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-quick-measures/

the rolling average allows you to create the code, i dont have a lot of experience with rolling averages but i think this could maybe solve your issue?

[Preetish_1 · Accepted solution]

Hi @vanessafvg,

I couldn't really figure out a way to make it work in quick measures, but I finally got it to work with this DAX formula.

M_Moving AVG =
var
LastDt = LASTDATE(T1[C_FullDate].[Date])
RETURN
CALCULATE(
     DIVIDE(COUNTROWS(FILTER(T1; T1[Overall Measure] = "Pass"));COUNTROWS(T1);0);
      DATESBETWEEN( T1[C_FullDate].[Date]; DATEADD(LastDt; -3; MONTH); LastDt))

Thank you for the support.

Preetish

[vanessafvg]

well done @Preetish_1