Forum Discussion

lcasey's avatar
lcasey
Post 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

Anonymous's avatar
Anonymous
Not applicable
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

  • lcasey's avatar
    lcasey
    Post Prodigy
    9 years ago

    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!