Don't want Row totals to change with drill down (Matrix)

[Twilla]

Hi all,

I am using the Matrix that is in Preview. I have several row levels to drill down. Year->Week ->Product categories. In the example below you see year 2012 week 7 and categories 12,13,... The subtotals and grand total all show the same values which are the sum of the other rows. However I would like that the subtotal for the year 2010 shows the sum over all weeks and not just week 7, even when I drill down to week 7. I would also like that the grand total stays the sum of all values,  and doesn't adapt when I drill down.

Does someone know if this is possible? If so could you explain me how to do so? Thanks

Cheers, Twilla

[v-yulgu-msft · Accepted solution]

Hi @Twilla,

Currently, it is not possible to achieve your requirement that keep the total row values no matter whether we apply filters to matrix. As you can see, the detailed rows, as well as total rows will be filtered automatically based on the selection.

As a workaround, you can create a measure which returns the sum over all weeks for each year.

Total value =
CALCULATE ( SUM ( Sheet1[Sales] ), ALLEXCEPT ( Sheet1, Sheet1[Date].[Year] ) )

Best regards,
Yuliana Gu