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

## Totals in Matrix Table

Hi guys,

this may be a stupid question but I can't find the answer.

Can I modify the formula of the Total in a matrix table?

I try to explain better with an example.

These are my data:

And I wuold like to achive this result:

In Power BI, to create the column Value of the matrix (second image), I use the measure Average (Data[Value]).

In this way, the Subtotals are right (average of the relative Value), but this make that also the Total be an average, instead I would like that the Total to be the SUM of the AVERAGE.

Have you some ideas to solve this problem?

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Totals in Matrix Table

I think you need to specify the sum yourself, e.g.:

```AvgThenSum =
VAR Avg_Basic =
AVERAGE ( Table1[Value] )
VAR Sum_Avg_Cat =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Table1, Table1[Category] ),
"AvgPerCat", CALCULATE ( AVERAGE ( Table1[Value] ) )
),
[AvgPerCat]
)
RETURN
IF ( HASONEVALUE ( Table1[Year] ), Avg_Basic, Sum_Avg_Cat )```
4 REPLIES 4
Super User

## Re: Totals in Matrix Table

I think you need to specify the sum yourself, e.g.:

```AvgThenSum =
VAR Avg_Basic =
AVERAGE ( Table1[Value] )
VAR Sum_Avg_Cat =
SUMX (
ADDCOLUMNS (
SUMMARIZE ( Table1, Table1[Category] ),
"AvgPerCat", CALCULATE ( AVERAGE ( Table1[Value] ) )
),
[AvgPerCat]
)
RETURN
IF ( HASONEVALUE ( Table1[Year] ), Avg_Basic, Sum_Avg_Cat )```
Frequent Visitor

## Re: Totals in Matrix Table

Hi @Stachu, and thank you for your answer.

Unfortunately I'm using Power BI only from few month, and I don't know already well the functionalities.

Can you explain me, in datail, the code you have posted?

Sorry me for this request.

Thank you.

Super User

## Re: Totals in Matrix Table

this may be helpful to understand DAX better
https://www.powerpivotpro.com/wp-content/uploads/2015/10/PowerPivotPro-Reference-Card.pdf

regarding the code itself

• Capital letters are DAX functions AVERAGE, etc.
• VAR is variable declaration
• table[column] is referencing a column named 'column' in a table named 'table'

the code works based on a premise that the data table (as shown in 'These are my data:') name is Table1

I hope that helps

Frequent Visitor

## Re: Totals in Matrix Table

Ok, I'm going to learn the DAX and to try your code.

Thank you @Stachu