Matrix Visual have the Grand Total be Average instead of Sum

[rmcgrath]

I swear I have done this in the past pretty easily, but how do I show Average instead of Total in a Matrix Visual (for the Grand Column Total)?

I thought there was a quick way to do this in the Visualizations Pane?  Did that feature get removed?  I'm currently using Power BI Version 2.138.1452.0 64-bit (November 2024)

[rmcgrath]

Another weird thing going on:  look what it does to the Total when I switch to Price:

I have no idea what that amount "0.125" is supposed to represent??

Here is the Total when I switch to Quantity:

[hnguy71 · Accepted solution]

Hi @rmcgrath 

The total can be adjusted to be any aggregate value. The 0.125 could represent a min, max, first, last, total, avg... we wouldn't know unless we dive deeper into your model...

with that being said, it's very possible to show the average of the totals (and only at the total level). Since your data is confidential and you cannot provide us with some sample data with sample results, you'll need to create an average expression and then either check to see if your row total is in scope using one or more of the combination: ISINSCOPE, HASONEVALUE, HASONEFILTER, ISFILTERED. If the value of the row and column context is not in scope, then that's your total line. Then, you'll tell DAX to return your average expression instead of the total. 

Hope that helps. 

[OwenAuger]

Hi @rmcgrath 

If you add a quick measure to the existing matrix (by right-clicking on the existing measure in the Values field well or on the ribbon) and choose "Average per category", it should auto-populate with the existing grouping field and measure displayed in the matrix.

I tested it myself with a matrix showing Sales Amount by Week Start and the Quick Measure pane looked like this:

Does this work for you?

[rmcgrath]

Unfortunately, no.  I forgot to mention I have a paramenter where the user can select between price and quantity.  I wonder if that's why it did not work for me?

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[rmcgrath]

I can't do that - it contains private data