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

## Set matrix total to a sum when column measure is an average calculation 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

## 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: 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
4 REPLIES 4
bhpage 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: 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
Akshaya 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)  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

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

Hi,

Try this

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements #### 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. Learn the answers to some of the questions asked during the Amanda Triple A event. #### 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.  Top Kudoed Authors
Users Online
Currently online: 284 members 2,822 guests
Recent signins:
• V-pazhen-msft • sem1 • bharathkumar • sandrahilala • ncbshiva • kosmiz • fabbimk • Milutin • harikumar • PStokes • zenisekd • arul4online • jiahao_chen12 • JorDan_23 