Matrix Table Aggregated Values

[TimmK]

I have the matrix table below which shows the consumption of each item per year/month.

Now for each row on the right side after the last column I want to show the AVG. Is this possible? I do not want to have the AVG for each year/month, but for the entire row.

[Anonymous]

Hi @TimmK ,

Sorry for my late reply.

According to my understanding, you want to change the default value of Row subtotals,right?

You could use ISINSCOPE() like this:

Measure = IF(ISINSCOPE('Table'[Product]),SUM('Table'[Price]),AVERAGE('Table'[Price]))

The final output is shown below:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tex628]

Hi @TimmK , 

This should be possible, first you need to turn on the subtotals on column level, you can further modify the subtotals to only calculate on the first level. 

Next up you need to modify your measure to perform a different calculation when the total is calculated. This can be done by using a check with ISFILTERED() to determine if there is any filter active in the calendar table. The will mean that you can differentiate between the normal cells inside the matrix and the total being calculated at the far right.

When the total is calculated there are no filters on the calendar table being applied while when you're calculating an individual cell there will be a filter on month. 

Give it a try and let me know how it goes!

/ J