cancel
Showing results for
Search instead for
Did you mean:
zzy79553927 Frequent Visitor

## Change subtotal calculation of matrix from Average to Sum 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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-jiascu-msft Super Contributor

## Re: Change subtotal calculation of matrix from Average to Sum

Hi @zzy79553927,

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 REPLIES 5 Super User

## Re: Change subtotal calculation of matrix from Average to Sum

Hi @zzy79553927,

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

zzy79553927 Frequent Visitor

## Re: Change subtotal calculation of matrix from Average to Sum

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. Super User

## Re: Change subtotal calculation of matrix from Average to Sum

Hi @zzy79553927,

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

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

v-jiascu-msft Super Contributor

## Re: Change subtotal calculation of matrix from Average to Sum

Hi @zzy79553927,

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
zzy79553927 Frequent Visitor

## Re: Change subtotal calculation of matrix from Average to Sum

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!!

## Helpful resources

Announcements Top Ideas Top Kudoed Authors
Users Online
Currently online: 43 members 923 guests
Recent signins:
• calerof • Oliverpmone • • Yazdanifard • Amardeep100115 • HolgerS • Please welcome our newest community members:
• KAD_DD • Ki9890 • charlistos365 • Adit • Willemsch • ay80 • anu2021 