Custom Subtotals in Matrix

[Elias-AR]

Hi,

I have a matrix with different measures shown as columns and a Product Dim as rows.

I would like to add a subtotal for all the products except "Search" Call it "Recurring Total" then add Search then have the overall total. is there a way to do it? I know I can group all the products in a "Recurring" cat in the product dim table and add it to the matrix as row, but is there a workaround without touching the dim tables? thanks

[danextian · Accepted solution]

Hi @Elias-AR 

Those extra rows must exist physically in your model as you can't assign a value to a row that doesn't exist - so you either modify your dimension table or create a new one. In the example below, Difference is the delta between the latest and the month before that and is an extra row in a custom dimension table.

A measure is then created that references the Period column in this dimension table returning the value for each period and whethey they should be shown and the Difference depending on the  period selected in the slicer. Please see the attached sample pbix.