Forum Discussion

mrbajana's avatar
mrbajana
Icon for Helper III rankHelper III
8 years ago

Change from sum to average in a Matrix total

It is possible to change the subtotals of a Matrix from sum to average.  
Need Help
mrbajana's avatar
mrbajana
Icon for Helper III rankHelper III
8 years ago

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. 

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

     

    Thanks

    Aditya