Averages

[lcasey]

Hello,

Is there any way to average all the years together on a last column?

I have a revenue expense report that users can slice by year. They can select multiple years and it works really well. The problem is that I need to add an Average for all the years selected and make it the last column.

Is this even possible with Power BI?  We do it in SSRS with no issues.

[v-shex-msft · Accepted solution]

Hi @lcasey,

You can try to use measure with condition to filter the calculate on total row.

Modify Amount = 
 IF(SUM('Table'[Amount])=SUMX(FILTER(ALL('Table'),[Date].[Month]=MAX([Date].[Month])),'Table'[Amount]),
		"row total",//total row
		IF(SUM('Table'[Amount])=SUMX(ALL('Table'),[Amount]),
			"table total",//total table
			SUM('Table'[Amount])))

Sample :

Modify Amount = 
 IF(SUM('Table'[Amount])=SUMX(FILTER(ALL('Table'),[Date].[Month]=MAX([Date].[Month])),'Table'[Amount]),
		AVERAGEX(FILTER(SUMMARIZE(ALL('Table'),[Date].[Month],[Date].[Year],"Total",SUM('Table'[Amount])),[Date].[Month]=MAX('Table'[Date].[Month])),[Total]),//total row
		IF(SUM('Table'[Amount])=SUMX(ALL('Table'),[Amount]),
			AVERAGEX(SUMMARIZE(ALL('Table'),[Date].[Year],"Total",SUM('Table'[Amount])),[Total]),//total table
			SUM('Table'[Amount])))

Regards,

Xiaoxin Sheng

[lcasey]

Thank You!

I am still working on getting this working and will update you soon.  Based on the pictures , this is exactly what I need and now I just need to understand the formula you gave me. 

I just wanted to drop you a quick note and let you know I really appreciate your help with this.

Thanks!

[mattbrice]

You can try this:

New Measure =
    AVERAGEX ( VALUES ( Calendar[Year] ), [Value Measure] ),

Where "[Value Measure]" is the name of the measure you are using in your pivot.  If the measure you have now is just a basic SUM( Table[Column] ) fore each year it will average = yearly amount, and grand total should be average over all years.