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

Change subtotal calculation of matrix from Average to Sum

微信截图_20170824100552.png

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
Community Support Team
Community Support Team

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




Highlighted
zzy79553927 Frequent Visitor
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
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!




Community Support Team
Community Support Team

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