Average below total in table or matrix

[mypowerbi1]

Hi,

I have to show average of the fields just below the total. There should be one more row for Average of Profit, Revenue and Tax field just below the total row. Please suggest how to do this.

Thanks

[v-jingzhang · Accepted solution]

Hi @mypowerbi1 

Currently the table visual doesn't support to have two total rows at bottom. To display Total and Average values at the same time, you can create a table similar to below to get the Total and Average results in advance. Then append it to the original table with UNION function into a new calculated table. Or you can also select necessary columns from the original table and aggregate them before appending. 

For example, original data table is like below

You can use the following code to get a new table. Drag columns from this new table into a table visual. And turn off Totals option. 

Table 3 = 
var __table2 = 
{
    ( "Total", BLANK (), SUM ( 'Table'[Profit] ), SUM ( 'Table'[Revenue] ), SUM ( 'Table'[Tax] ) ),
    ( "Average", BLANK (), AVERAGE ( 'Table'[Profit] ), AVERAGE ( 'Table'[Revenue] ), AVERAGE ( 'Table'[Tax] ) )
}
return
UNION('Table',__table2)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.