cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
roboskoog Frequent Visitor
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
Super User
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


1 REPLY 1
Super User
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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!