Totals in Matrix Table

[matteocarnelos]

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.

[Stachu · Accepted solution]

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 )

[matteocarnelos]

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.

[Stachu]

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

[matteocarnelos]

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

Thank you @Stachu