How to edit the Matrix Sum Total Row to show Average or Median?

[levered_up]

I have created a very simple matrix, looking roughly like the below. The PBI matrix tool automatically calculates the total of all the values for each name across the years.

Is there a way that I can alter/append a 'AVERAGE' (or 'MEDIAN') row to the bottom of the matrix?

Thank you very much in advance (and please explain in simple language - I am new to PBI!)

P.S. I have searched far and wide for an answer to this, but was unable to find a solution where the average is added as a row, as opposed to a column.

	Stock A	Stock B	Stock C
2001	5%	1%	9%
2002	4%	2%	10%
2003	6%	3%	11%
Total	15%	6%	20%

[nandukrishnavs · Accepted solution]

@levered_up 

Sample Table

Year	Stock	Value
2011	A	0.02
2012	B	0.12
2013	C	0.08
2011	B	0.13
2012	C	0.16
2013	A	0.2
2011	C	0.07
2012	A	0.11
2013	B	0.01

Write a DAX measure

Measure = 
var _x= SELECTEDVALUE('Table'[Value])
var _total= AVERAGE('Table'[Value])
var _result= IF(HASONEVALUE('Table'[Year]),_x,_total)
return _result

Use this measure in the Matrix visual

You can change the row subtotal label from the below settings.

 

Please refer to the attached pbix file

[levered_up]

Would you know how I go about adding another "subtotal" row below it for the median?

Thank you for the great answer though! This worked a treat. Accepting as solution.

[amitchandak]

@levered_up , Not very clear. But you can you can use is filtered or has one value

example

if(isfiltered(Table[Year]) ,[Measure],averageX(Values(Table[Year]), [Measure]))

this will avg by year averageX(Values(Table[Year]), [Measure])

Also, refer

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[Sumanth_23]

hi @levered_up - You can change the calculation method for the particular column in the matrix as seen below

Please mark the post as a solution and provide a 👍 if my comment helped with solving your issue. Thanks!

[levered_up]

Hi @Sumanth_23 , thank you for your quick reply.

When I try and do the same as you, I am not presented with the same options?