cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrbajana Frequent Visitor
Frequent Visitor

Change from sum to average in a Matrix total

It is possible to change the subtotals of a Matrix from sum to average.

2018-05-29_10h51_19.png

 

5 REPLIES 5
adityakommu Member
Member

Re: Change from sum to average in a Matrix total

Hello,

 

The way grand total works is based on the column if you take Sum in the column it will give a grand total of the sum if you want average change you calculation to average and grand total will be based on average.

 

Let me know if this not you are expecting or give us sample data.

mrbajana Frequent Visitor
Frequent Visitor

Re: Change from sum to average in a Matrix total

Thanks for your answer, the issue is that the columns I need with the sum , but at the end in the subtotal I Need an average based on the sum columns. 

Highlighted
adityakommu Member
Member

Re: Change from sum to average in a Matrix total

Hi,

 

So i have created 2 calculations

 

 

Avg and Sum

 

 

I am checking if total count of columns= total rows in a table then give me "average" else give me "
"sum".

 

Sum = CALCULATE(SUM('Table 1'[SPHA's Filled]),ALLEXCEPT('Table 1','Table 1'[Job: Name])) ( which will give you sum)

Avg =CALCULATE(AVERAGE('Table 1'[SPHA's Filled]),ALLEXCEPT('Table 1','Table 1'[Job: Name]))  (which will give you average)

 

 Grand total Formula  :

 

Total = if(COUNT(a[write your column])=COUNTROWS('Table 1'),[avg],[sum])

 

 

Thanks,

Aditya

adityakommu Member
Member

Re: Change from sum to average in a Matrix total

If you have got it, Please mark this as closed.

 

Thanks

Aditya

Community Support Team
Community Support Team

Re: Change from sum to average in a Matrix total

@mrbajana,

 

You may refer to the following post.

https://community.powerbi.com/t5/Desktop/monthly-average-of-daily-sums/m-p/291451#M128396

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.