cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
matteocarnelos Frequent Visitor
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:

data.JPG

 

 

 

 

 

 

 

 

 

 

And I wuold like to achive this result:

result.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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
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
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 )
matteocarnelos Frequent Visitor
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
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

matteocarnelos Frequent Visitor
Frequent Visitor

Re: Totals in Matrix Table

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

 

Thank you @Stachu