cancel
Showing results for
Did you mean:
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
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

Proud to be a Datanaut!

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.

Highlighted
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

Proud to be a Datanaut!

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

Announcements

Power BI Super User, Greg Deckler, explains

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Back to School Contest

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 42 members 1,100 guests
Recent signins: