Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
danguy2099
Frequent Visitor

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.
1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

1.png2.png3.png4.png

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

View solution in original post

2 REPLIES 2
danguy2099
Frequent Visitor

Works as intended. Thanks

tamerj1
Super User
Super User

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

1.png2.png3.png4.png

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors