Matrix table using multiple columns

[jamesbb93]

Hi,

Seasons greetings to everyone!  I'm hoping someone could help me with something I feel is pretty complex, but it's probably a simple fix!

I have a power query table with the following data (snippet below and fuller example of dataset attached)

ID	Date	Total Trg Hours Total	Avoiding Action Score	Scan Score	Division of Attention Score
54	01/12/2021	01:45	-2	0	0
56	03/12/2021	02:00	1	0
57	03/12/2021	02:30	0		-1

I would like to show a COUNT of the data in a matrix table in the following format.

	-2	-1	0	1
Avoiding Action	1	0	1	0
Scan	0	0	2	0
Division of Attention	0	1	1	0

To give a better example of just what I'm looking for, see PDF link of the report the table will be going into. I've managed to recreate exactly what I'm looking for using a UNION table, but because there's no relationships, it's not filterable against the rest of the report (problem!!)

Really hope someone can help with this.

Thanks in advance and Happy New Year!

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

Result: =
CALCULATE (
COUNTROWS ( FILTER ( 'Table', 'Table'[Value] <> BLANK () ) ),
FILTER ( 'Table', 'Table'[Value] = MAX ( 'Table'[Value] ) )
)

[amitchandak · Accepted solution]

@jamesbb93 , Unpivot the columns from F to AB in your excel in power query, then you have both headers and values as columns, you can use them in matrix as row and column and count of any one as values

https://radacad.com/pivot-and-unpivot-with-power-bi

[jamesbb93]

@amitchandak @Jihwan_Kim I wasn't expecting such quick replies.

I went with the Unpivot route as it worked better with my data model.

Really appreciate the help!