Forum Discussion
Customize subtotal in matrix
Try this. If this sitll doens't work, there are paid custom visuals that allow insertion of custom rows.
Hi Jessica_17
You will need a disconnected table to be used as a display table and then create a measure referencing this table that conditionally return values. You can use DAX but if the categories aren't fixed you can just use enter data. Your goal is to position Subtotal right before the last two categories
Subtotal01 =
VAR _category =
SELECTEDVALUE ( DisplayTable[Category] )
VAR _subtotal =
CALCULATE ( [Value1], KEEPFILTERS ( NOT 'Table'[Category] IN { "AFGH", "E" } ) )
VAR _other =
CALCULATE (
[Value1],
TREATAS ( VALUES ( DisplayTable[Category] ), 'Table'[Category] )
)
RETURN
SWITCH (
TRUE (),
ISINSCOPE ( 'Table'[Sub Catgory] ) && _category = "Subtotal", BLANK (),
_category = "Subtotal", _subtotal,
_other
)
===========================================
Subtotal02 =
VAR _category =
SELECTEDVALUE ( DisplayTable[Category] )
VAR _subtotal =
CALCULATE ( [Value1], KEEPFILTERS ( NOT 'Table'[Category] IN { "AFGH", "E" } ) )
VAR _other =
CALCULATE (
[Value1],
TREATAS ( VALUES ( DisplayTable[Category] ), 'Table'[Category] )
)
RETURN
SWITCH (
TRUE (),
_category = "Subtotal", _subtotal,
_other
)
Please see the attached pbix.
HI danextian ,
Thanks for the response.
When I tried the same approach with my dataset, the subtotal is displaying all the subcategory names alongside it. Because of this, when I drill down, I have to manually drill back up to view only the subtotal.
Is there an alternative solution to handle this, or would you need any additional information from my side to investigate further?
Try this. If this sitll doens't work, there are paid custom visuals that allow insertion of custom rows.
Hello danextian ,
Thanks for the solution, it worked!!