Forum Discussion

lcasey's avatar
lcasey
Icon for Post Prodigy rankPost Prodigy
9 years ago
Solved

Averages

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 we...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

mattbrice's avatar
mattbrice
Icon for Solution Sage rankSolution Sage
9 years ago

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.