cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
johankent30 Regular Visitor
Regular Visitor

Set matrix total to a sum when column measure is an average calculation

matrix.JPG

 

 

I am trying to sum an average calculation measure in a matrix. As you can see in the pic above, I want the monthly averages for income, but the total to be a sum instead of an average. Does anyone know how to accomplish this? Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
bhpage Member
Member

Re: Set matrix total to a sum when column measure is an average calculation

Hi,

 

It is hard to craft the exact solution for you without seeing a sample data set that is producing the numbers in your screenshot. However, I think this is possible. You can manipulate totals in Power BI by leveraging the HASONEVALUE() function. I created a sample data set with one record per month, and the values reflect your monthly averages. By default, when I throw that into a matrix, it sums the values (which I think is actually the output you're looking for). I suspect you have many records in your dataset and then are computing an average at the monthly level. However, you can see that I've written a measure called "Measure" that shows 0 for the total. The syntax looks like this:

 

Measure =
IF (
HASONEVALUE ( Sheet1[Month] ),
SUM(Sheet1[Value]),
0
)
 
And the output looks like this:
manipulating totals.PNG

I suspect you'll want to do something similar, but perhaps something like:

 

Measure =
IF (
HASONEVALUE ( Sheet1[Month] ),
AVG(Sheet1[Value]),
SUMX(Sheet1[Value])
)
 
Let me know if this is helpful, or if I have misunderstood your requirement.
 
Thanks,
Ben

View solution in original post

4 REPLIES 4
bhpage Member
Member

Re: Set matrix total to a sum when column measure is an average calculation

Hi,

 

It is hard to craft the exact solution for you without seeing a sample data set that is producing the numbers in your screenshot. However, I think this is possible. You can manipulate totals in Power BI by leveraging the HASONEVALUE() function. I created a sample data set with one record per month, and the values reflect your monthly averages. By default, when I throw that into a matrix, it sums the values (which I think is actually the output you're looking for). I suspect you have many records in your dataset and then are computing an average at the monthly level. However, you can see that I've written a measure called "Measure" that shows 0 for the total. The syntax looks like this:

 

Measure =
IF (
HASONEVALUE ( Sheet1[Month] ),
SUM(Sheet1[Value]),
0
)
 
And the output looks like this:
manipulating totals.PNG

I suspect you'll want to do something similar, but perhaps something like:

 

Measure =
IF (
HASONEVALUE ( Sheet1[Month] ),
AVG(Sheet1[Value]),
SUMX(Sheet1[Value])
)
 
Let me know if this is helpful, or if I have misunderstood your requirement.
 
Thanks,
Ben

View solution in original post

Akshaya Established Member
Established Member

Re: Set matrix total to a sum when column measure is an average calculation

 

@johankent30  You can do 

 

Avg_Income = AVERAGE(Table[Income])

For sum of the average: Sum_Avg = SUMX(VALUES(Table[Month]), [Average Income])

 

It will look as follows: (Income is the data you already have for each month)

AvgSum_1.PNG

Super User
Super User

Re: Set matrix total to a sum when column measure is an average calculation

Unfortunately, there's no easy way to accomplish this in PowerBI, despite it being an incredibly common use case.  You can vote for the idea here to make it easier. 

 

So the easy way to solve your question is to realize that measures are recalculated with a different context each time they show up in a matrix or table.  So you create a measure that acts differently depending on whether it's a regular row or a total row.  I'll do my best with syntax, but not knowing your table names or the lowest level grouping in your visual makes this hard.  Here's my best guess:

AverageIncomeForMatrix = IF(ISINSCOPE(table[Month]), AVERAGE(table[a-Income]), SUMX(table, table[Month], AVERAGE(table[a-Income])))

 

This uses ISINSCOPE instead of HASONEVALUE, because you can run into some strange results if you've only got one row/column in the matrix that's being averaged/totaled.  ISINSCOPE is able to actually check the current context of a matrix, which will always be correct.

Super User
Super User

Re: Set matrix total to a sum when column measure is an average calculation

Hi,

Try this

=IF(HASONEVALUE(Calendar[Month]),[your measure],SUMX(SUMMARIZE(VALUES(Calendar[Month]),Calendar[Month],"Total",[your measure]),[Total]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 284 members 2,822 guests
Please welcome our newest community members: