Subtotals in Matrix as Columns

[pk_1002]

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

[v-kongfanf-msft]

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

[danextian · Accepted solution]

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.

[pk_1002]

many thanks! I made a disconnected table.

[danextian]

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.