change the value of the Grand total from sum of values to average in the matrix visual

[vishnuprashanth]

I have a matrix visual like this attached. Is there any way I could change the grand total to the average value? 

Even in the Table view, I still have only the total value, instead, I want the average value to be present at the end of my table.

Matrix

Any suggestions to get achieve this, please?

PS: I don't want to change my values inside the matrix or tables to change to average and then sum it. 

[ricardocamargos]

Hi @vishnuprashanth,

You can do it by using the function HASONEVALUE, also you can change the calculation if necessary.

https://msdn.microsoft.com/en-us/query-bi/dax/hasonevalue-function-dax

[vishnuprashanth]

Hi @ricardocamargos,

So I create the below HASONEVALUE func. This just calculates for the energy column. I want to create a measure that calculates for all the columns. 

IF (
    HASONEVALUE ( Table[athlete] ),
    SUM ( Table[Total Energy] ),
    AVERAGE (Table[Total Energy] )
)

 
Thanks. 

[Anonymous]

@vishnuprashanth

What if this measure is part of a Power BI matrix table and there are multiple fields under "Rows" which user might put in with their self service capabilities.

In that case, I cant choose to put HASONEVALUE for a single column and I cant HASONEVALUE for multiple columns.

Any suggestions on how we should do it in such case?

Regards

[ricardocamargos]

Hi @vishnuprashanth,

I think you have to create a measure for each column and customize the total for each one.

[vishnuprashanth]

@ricardocamargos Thanks Ricardo 🙂 

[vishnuprashanth]

Hi @ricardocamargos, 

Thanks for the suggestion. I read the HASONEVALUE function you had shared, but I still don't understand how it works and what attributes I need to give for the HASONEVALUE function. 

My Matrix visual and table are dynamic, I would want to change/select the date, name and other attributes. Could I still use HASONEVALUE for this? What is the change of calculation you were mentioning about? 

I am really sorry, very new to Power BI and DAX. Still learning them