Forum Discussion
matteocarnelos
Advocate III
Advocate IIITotals 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 )
Stachu
Community Champion
Community ChampionI 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 )
matteocarnelosAdvocate III
Advocate IIIHi 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.pdfregarding 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