cancel
Showing results for
Did you mean:
Highlighted
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

## 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.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

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.

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 91 members 1,636 guests
Recent signins: