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
johankent30
Helper I
Helper I

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

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
Ashish_Mathur
Super User
Super User

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/
Cmcmahan
Resident Rockstar
Resident Rockstar

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.

Anonymous
Not applicable

 

@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

Anonymous
Not applicable

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

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.