Hi all, I have a following dimension table: And I would like to have a Matrix visual with the following structure:Could you please help me how to get these two columns (Category1 and Category2) at the end? Thanks a lot

Hi pk_1002, You will need a disconnected table that will hold that column names and a measure (or measures in your case) to return the value based on the current column name. Attached is a sample pbix with a similar use case.

Subtotals in Matrix as Columns | Microsoft Fabric Community

4 Replies

danextian
Super User
2 years ago
Hi pk_1002,

You will need a disconnected table that will hold that column names and a measure (or measures in your case) to return the value based on the current column name.

Attached is a sample pbix with a similar use case.
Custom Period Columns.pbix852 KB
- pk_1002
  Frequent Visitor
  2 years ago
  many thanks! I made a disconnected table.
  - danextian
    Super User
    2 years ago
    Regarding the disconnected table and if your categories and subcategories are ever changing or a new ones are ocassionaly added, you can try the following calculated table
    
    Disconnected Table = VAR SubCat = SELECTCOLUMNS ( 'table', "Column", 'table'[subcategory], "Type", "Subcategory" ) VAR _Cat = SELECTCOLUMNS ( 'table', "Column", 'table'[category], "Type", "Category" ) RETURN ADDCOLUMNS ( DISTINCT ( UNION ( SubCat, _Cat ) ), "Sort", IF ( [Type] = "Category", 1 ) )
    
    Sort Column by Sort. This is so subcategories always come first.
Anonymous
Not applicable
2 years ago
Hi pk_1002 ,

Did danextian reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best Regards,
Adamk Kong

Forum Discussion

Subtotals in Matrix as Columns