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
Solved! Go to Solution.
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:
I suspect you'll want to do something similar, but perhaps something like:
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:
I suspect you'll want to do something similar, but perhaps something like:
@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)
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.
Hi,
Try this
=IF(HASONEVALUE(Calendar[Month]),[your measure],SUMX(SUMMARIZE(VALUES(Calendar[Month]),Calendar[Month],"Total",[your measure]),[Total]))
Hope this helps.
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 was a busy month in the community. Read the recap article to learn about some of the events and content.
User | Count |
---|---|
64 | |
63 | |
60 | |
50 | |
46 |