Change subtotal calculation of matrix from Average to Sum

[Anonymous]

Hi, I am just creating a matrix in power bi.

The [Carry Over Revenue FY 17] is a measure that I put into this matrix. 

Here is the calculation: 

Carry Over Revenue FY 17 = AVERAGE(CRM_Fact_SalesOpportunities[CarryOverRevenueFY17])

When I done this, I always get the average number of this column in Subtotal box automatically. But I wanna get the sum number of this column. How can I fix this?

[MFelix]

Hi @Anonymous,

You need to do a SUMX of a summarize of your average something like this:

AverageTotal =
SUMX (
    SUMMARIZE ( Table; Group_Column; "Average"; AVERAGE ( Table[Column] ) );
    AVERAGE ( Table [Column]  )
)

No sure How you have your table set up but chenge the columns by you data.

Regards,

MFelix

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Hi MFelix, @MFelix

Just an information, the red part should be [Average], which is from the blue part.

AverageTotal =
SUMX (
    SUMMARIZE ( Table; Group_Column; "Average"; AVERAGE ( Table[Column] ) );
    AVERAGE ( Table [Column]  )
)

Best Regards!

Dale

[sjursik]

How do you edit the DAX formulas for the matrix subtotal? I can't seem to find the formula/code to edit (as shown in the yellow boxes).

Thanks!

[sjursik]

How do you navigate to edit the DAX formulas for the matrix subtotal?

Thanks!

[Anonymous]

Thanks @MFelix for giving this great idea. It gave me the right direction.

Thanks @v-jiascu-msft for pointing out this a little thing. It works great!!

[Anonymous]

Hi @MFelix,

Your solution seems very close to what I want.

I used your codes and I found that some values are correct and some are not. Could you tell me what is the 'Table' in your codes? Which 'Table' should I use in this case. By the way, I have two group columns which are located in two tables.

[MFelix]

Hi @Anonymous,

The table you should use is the one where you want to calculate the average values, I made the expression more generic since there wasn't much detail in the way your data is setup.

Regards,
MFelix