how do I use specific values from two different columns in a table as column headers in a matrix

[nk19330]

Hello,

I have data in this format: 

and I have to present it in the below format in a matrix visual where M1& M2 are measures calculated from col4 and ct = rowcount for the category or subcaetegory

Please suggest how to achive this?

TIA !

[Anonymous]

Hi @nk19330 ,
Thank you @Irwan for the prompt response!
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

[Irwan · Accepted solution]

hello @nk19330 

please check if this accomodate your need.

if the Category 3 will be always split, i would do somehting like below.

1. create a new custom column with following code to create a column you want to use as matrix header

if [Column2]="Category 1" or [Column2]="Category 2" then [Column2] else [Column3]

2. plot the custom column into matrix visual along with all measures.

Hope this will help.

Thank you.