Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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.

View solution in original post

7 REPLIES 7
MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



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

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.

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!

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

 

Thanks!

Anonymous
Not applicable

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
Not applicable

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.

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

Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.