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: Category Sum A 25 B 28 C 33...
  • 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] )
)
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] )
)