topic Re: Sum average columns values in Sub Totals on Matrix visual in DAX Commands and Tips

Sum average columns values in Sub Totals on Matrix visual

lucashmcury — Tue, 29 Nov 2022 15:25:29 GMT

hi guys,

I am using one Matrix visual, as per below image, with column "Category" in Coumns, Values column in Values and Company in rows

But the Column Sub Total is showing me the average of all columns...and I need the sum

Below is the dax formula I am using to get the average by each column

Average of Values average per Company Names =

AVERAGEX(

KEEPFILTERS(VALUES('Company'[Names])),

CALCULATE(AVERAGE('Values'[Values]))

)

How can I set the Column Sub Totals to show the sum of all columns averages?

Re: Sum average columns values in Sub Totals on Matrix visual

tamerj1 — Tue, 29 Nov 2022 16:04:26 GMT

Hi lucashmcury
Please try

Average of Values average per Company Names = VAR NormalAverage = AVERAGEX ( KEEPFILTERS ( VALUES ( 'Company'[Names] ) ), CALCULATE ( AVERAGE ( 'Values'[Values] ) ) ) VAR SumOfAverage = SUMX ( VALUES ( 'Table'[Category] ), CALCULATE ( AVERAGEX ( KEEPFILTERS ( VALUES ( 'Company'[Names] ) ), CALCULATE ( AVERAGE ( 'Values'[Values] ) ) ) ) ) RETURN IF ( ISINSCOPE ( 'Table'[Category] ), NormalAverage, SumOfAverage )

Re: Sum average columns values in Sub Totals on Matrix visual

lucashmcury — Tue, 29 Nov 2022 16:42:10 GMT

thank you!

you are a master!