Calculated rows in matrix table

[danguy2099]

I like to create a matrix table that show both my categories and a partial subgroup in one column in the same table. My expected output is like this:

Category	Sum
A	25
B	28
C	33
Subcategory (A+B+C)	86
D	34
Grand Total	120

Dataset is like this:

Category	Value
A	12
A	23
B	13
B	15
C	16
C	17
D	15
D	19

• How would I achieve this by writing DAX? I believe solution is by writing some virtual tables and then put it in a matrix table but I dont know how to write this code. Hope someone can help me.

[danguy2099]

Works as intended. Thanks

[tamerj1 · Accepted solution]

Hi @danguy2099 
Not so pretty but it works. Start by creatinga filter table (Categories)

Categories = 
SELECTCOLUMNS ( 
    { ( "A", 1 ), ( "B", 2 ), ( "C", 3 ), ( "Subcategory (A+B+C)", 4 ), ( "D", 5 ) }, 
    "Category", [Value1], 
    "Index", [Value2] 
)

SUM = 
IF ( 
    ISEMPTY ( 'Dataset' ),
    CALCULATE (
        SUM ( 'Dataset'[Value] ),
        'Dataset'[Category] IN { "A", "B", "C" },
        ALL ( 'Dataset' )
    ),
    SUM ( 'Dataset'[Value] )
)