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

 

 

 

 

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

3 Replies

  • mattbrice's avatar
    mattbrice
    Solution 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.  

  • 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!