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.


I have book! Learn Power BI from Packt


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.


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 91 members 1,636 guests
Please welcome our newest community members: