Forum Discussion
Solved
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...
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 )
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
