Forum Discussion

danguy2099's avatar
danguy2099
Frequent Visitor
4 years ago
Solved

Calculated rows in matrix table

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:

CategorySum
A25
B28
C33
Subcategory (A+B+C)86
D34
Grand Total120

 

Dataset is like this:

 

CategoryValue
A12
A23
B13
B15
C16
C17
D15
D19
  • 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.
  • tamerj1's avatar
    tamerj1
    4 years ago

    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] )
)

2 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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] )
)