cancel
Showing results for
Did you mean:
Frequent Visitor

## How do I calculate an average by month that is not cumulative?

I am trying to calculate a monthly average for the field, FTE_Ratio, for the selected projects on my Dashboard (could be 1 could be more than 1).  When I use the following forumla:

FTE Ratio = CALCULATE(AVERAGE(EffortByMonth[FTE_Ratio]),(FILTER(ALLEXCEPT(EffortByMonth,Projects[Project ID and Name]),EffortByMonth[Reporting Month]<=MAX(EffortByMonth[Reporting Month]))))

I get a running average for the months corresponding to the selected projects (i.e. Month 1 = average of month 1 FTE_Ratios, Month 2 = Average of Month1 and Month 2 FTE_Ratios, Month 3 = Average of Months 1 thru 3 FTE_Ratio, etc.).  What i need is each month's distinct average (Month 1 = Average of Month 1 only, Month 2 = average of month 2 only, etc.).

How do I do this?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: How do I calculate an average by month that is not cumulative?

Should be something like:

FTE Ratio = CALCULATE(AVERAGE(EffortByMonth[FTE_Ratio]),(FILTER(ALLEXCEPT(EffortByMonth,Projects[Project ID and Name],EffortByMonth[Reporting Month])))

Put that measure in a table with EffortByMonth[Reporting Month] and it should filter each row by reporting month.

Proud to be a Datanaut!

Highlighted
Super User

## Re: How do I calculate an average by month that is not cumulative?

Should be something like:

FTE Ratio = CALCULATE(AVERAGE(EffortByMonth[FTE_Ratio]),(FILTER(ALLEXCEPT(EffortByMonth,Projects[Project ID and Name],EffortByMonth[Reporting Month])))

Put that measure in a table with EffortByMonth[Reporting Month] and it should filter each row by reporting month.