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
v-jiascu-msft Super Contributor
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
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
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!




v-jiascu-msft Super Contributor
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.
Highlighted
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!!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 107 members 1,677 guests
Please welcome our newest community members: