Create a Correlation Matrix in Power BI Matrix Visual

[datacauts]

Hi guys, how you doin?

Been working on a dashboard here and I need to plot a correlation matrix in one of my visuals.

Here's the data I have:

*this is a correlation matrix between funds, calculated via R script)
*it's on long (vertical) format because I have about 679 funds so it makes it clear for visualization AND that's one of the reasons why I can't use the Correlation Plot custom visual that Power BI offers

*the columns "CNPJ" and "Attribute" have the same group of values

What I want to do is to plot the data like this:

I tried to create an auxiliar related table (duplicated from the first one) and use the CNPJ column of table 1 for Rows and CNPJ column of table 2 for Columns, but it didn't work.

Do you have any suggestions?

Thanks!

[datacauts]

Hello again.

Here i have a sample .pbix dataset for this question, where I have already created a correlation matrix visual for all funds using an auxiliar Dimension Table:

https://ufile.io/fsjaxddn

Also, the code for the Long Database:

let
    Fonte = Web.BrowserContents("https://docs.google.com/spreadsheets/d/e/2PACX-1vQrjTHS5r6nJ-6tx81w3F5N4PydwWGZGjF4Wycg8mORQ2nFGucdrP_W1qmbB7x4TYSlTn5iaQVNdvFN/pubhtml"),
    #"Tabela extraída de HTML" = Html.Table(Fonte, {{"Column1", "TABLE.waffle > * > TR > :nth-child(1)"}, {"Column2", "TABLE.waffle > * > TR > :nth-child(2)"}, {"Column3", "TABLE.waffle > * > TR > :nth-child(3)"}, {"Column4", "TABLE.waffle > * > TR > :nth-child(4)"}, {"Column5", "TABLE.waffle > * > TR > :nth-child(5)"}, {"Column6", "TABLE.waffle > * > TR > :nth-child(6)"}, {"Column7", "TABLE.waffle > * > TR > :nth-child(7)"}, {"Column8", "TABLE.waffle > * > TR > :nth-child(8)"}, {"Column9", "TABLE.waffle > * > TR > :nth-child(9)"}, {"Column10", "TABLE.waffle > * > TR > :nth-child(10)"}, {"Column11", "TABLE.waffle > * > TR > :nth-child(11)"}, {"Column12", "TABLE.waffle > * > TR > :nth-child(12)"}, {"Column13", "TABLE.waffle > * > TR > :nth-child(13)"}, {"Column14", "TABLE.waffle > * > TR > :nth-child(14)"}, {"Column15", "TABLE.waffle > * > TR > :nth-child(15)"}, {"Column16", "TABLE.waffle > * > TR > :nth-child(16)"}, {"Column17", "TABLE.waffle > * > TR > :nth-child(17)"}, {"Column18", "TABLE.waffle > * > TR > :nth-child(18)"}, {"Column19", "TABLE.waffle > * > TR > :nth-child(19)"}, {"Column20", "TABLE.waffle > * > TR > :nth-child(20)"}, {"Column21", "TABLE.waffle > * > TR > :nth-child(21)"}, {"Column22", "TABLE.waffle > * > TR > :nth-child(22)"}, {"Column23", "TABLE.waffle > * > TR > :nth-child(23)"}, {"Column24", "TABLE.waffle > * > TR > :nth-child(24)"}, {"Column25", "TABLE.waffle > * > TR > :nth-child(25)"}, {"Column26", "TABLE.waffle > * > TR > :nth-child(26)"}, {"Column27", "TABLE.waffle > * > TR > :nth-child(27)"}, {"Column28", "TABLE.waffle > * > TR > :nth-child(28)"}, {"Column29", "TABLE.waffle > * > TR > :nth-child(29)"}, {"Column30", "TABLE.waffle > * > TR > :nth-child(30)"}, {"Column31", "TABLE.waffle > * > TR > :nth-child(31)"}, {"Column32", "TABLE.waffle > * > TR > :nth-child(32)"}, {"Column33", "TABLE.waffle > * > TR > :nth-child(33)"}, {"Column34", "TABLE.waffle > * > TR > :nth-child(34)"}, {"Column35", "TABLE.waffle > * > TR > :nth-child(35)"}, {"Column36", "TABLE.waffle > * > TR > :nth-child(36)"}, {"Column37", "TABLE.waffle > * > TR > :nth-child(37)"}, {"Column38", "TABLE.waffle > * > TR > :nth-child(38)"}, {"Column39", "TABLE.waffle > * > TR > :nth-child(39)"}, {"Column40", "TABLE.waffle > * > TR > :nth-child(40)"}, {"Column41", "TABLE.waffle > * > TR > :nth-child(41)"}, {"Column42", "TABLE.waffle > * > TR > :nth-child(42)"}, {"Column43", "TABLE.waffle > * > TR > :nth-child(43)"}, {"Column44", "TABLE.waffle > * > TR > :nth-child(44)"}, {"Column45", "TABLE.waffle > * > TR > :nth-child(45)"}, {"Column46", "TABLE.waffle > * > TR > :nth-child(46)"}, {"Column47", "TABLE.waffle > * > TR > :nth-child(47)"}, {"Column48", "TABLE.waffle > * > TR > :nth-child(48)"}, {"Column49", "TABLE.waffle > * > TR > :nth-child(49)"}, {"Column50", "TABLE.waffle > * > TR > :nth-child(50)"}, {"Column51", "TABLE.waffle > * > TR > :nth-child(51)"}, {"Column52", "TABLE.waffle > * > TR > :nth-child(52)"}, {"Column53", "TABLE.waffle > * > TR > :nth-child(53)"}, {"Column54", "TABLE.waffle > * > TR > :nth-child(54)"}, {"Column55", "TABLE.waffle > * > TR > :nth-child(55)"}, {"Column56", "TABLE.waffle > * > TR > :nth-child(56)"}, {"Column57", "TABLE.waffle > * > TR > :nth-child(57)"}, {"Column58", "TABLE.waffle > * > TR > :nth-child(58)"}, {"Column59", "TABLE.waffle > * > TR > :nth-child(59)"}, {"Column60", "TABLE.waffle > * > TR > :nth-child(60)"}, {"Column61", "TABLE.waffle > * > TR > :nth-child(61)"}, {"Column62", "TABLE.waffle > * > TR > :nth-child(62)"}, {"Column63", "TABLE.waffle > * > TR > :nth-child(63)"}, {"Column64", "TABLE.waffle > * > TR > :nth-child(64)"}, {"Column65", "TABLE.waffle > * > TR > :nth-child(65)"}, {"Column66", "TABLE.waffle > * > TR > :nth-child(66)"}, {"Column67", "TABLE.waffle > * > TR > :nth-child(67)"}, {"Column68", "TABLE.waffle > * > TR > :nth-child(68)"}, {"Column69", "TABLE.waffle > * > TR > :nth-child(69)"}, {"Column70", "TABLE.waffle > * > TR > :nth-child(70)"}, {"Column71", "TABLE.waffle > * > TR > :nth-child(71)"}, {"Column72", "TABLE.waffle > * > TR > :nth-child(72)"}, {"Column73", "TABLE.waffle > * > TR > :nth-child(73)"}, {"Column74", "TABLE.waffle > * > TR > :nth-child(74)"}, {"Column75", "TABLE.waffle > * > TR > :nth-child(75)"}, {"Column76", "TABLE.waffle > * > TR > :nth-child(76)"}, {"Column77", "TABLE.waffle > * > TR > :nth-child(77)"}, {"Column78", "TABLE.waffle > * > TR > :nth-child(78)"}, {"Column79", "TABLE.waffle > * > TR > :nth-child(79)"}, {"Column80", "TABLE.waffle > * > TR > :nth-child(80)"}, {"Column81", "TABLE.waffle > * > TR > :nth-child(81)"}, {"Column82", "TABLE.waffle > * > TR > :nth-child(82)"}, {"Column83", "TABLE.waffle > * > TR > :nth-child(83)"}, {"Column84", "TABLE.waffle > * > TR > :nth-child(84)"}, {"Column85", "TABLE.waffle > * > TR > :nth-child(85)"}, {"Column86", "TABLE.waffle > * > TR > :nth-child(86)"}, {"Column87", "TABLE.waffle > * > TR > :nth-child(87)"}, {"Column88", "TABLE.waffle > * > TR > :nth-child(88)"}, {"Column89", "TABLE.waffle > * > TR > :nth-child(89)"}, {"Column90", "TABLE.waffle > * > TR > :nth-child(90)"}, {"Column91", "TABLE.waffle > * > TR > :nth-child(91)"}, {"Column92", "TABLE.waffle > * > TR > :nth-child(92)"}, {"Column93", "TABLE.waffle > * > TR > :nth-child(93)"}, {"Column94", "TABLE.waffle > * > TR > :nth-child(94)"}, {"Column95", "TABLE.waffle > * > TR > :nth-child(95)"}, {"Column96", "TABLE.waffle > * > TR > :nth-child(96)"}, {"Column97", "TABLE.waffle > * > TR > :nth-child(97)"}, {"Column98", "TABLE.waffle > * > TR > :nth-child(98)"}, {"Column99", "TABLE.waffle > * > TR > :nth-child(99)"}, {"Column100", "TABLE.waffle > * > TR > :nth-child(100)"}, {"Column101", "TABLE.waffle > * > TR > :nth-child(101)"}, {"Column102", "TABLE.waffle > * > TR > :nth-child(102)"}, {"Column103", "TABLE.waffle > * > TR > :nth-child(103)"}, {"Column104", "TABLE.waffle > * > TR > :nth-child(104)"}, {"Column105", "TABLE.waffle > * > TR > :nth-child(105)"}, {"Column106", "TABLE.waffle > * > TR > :nth-child(106)"}, {"Column107", "TABLE.waffle > * > TR > :nth-child(107)"}, {"Column108", "TABLE.waffle > * > TR > :nth-child(108)"}, {"Column109", "TABLE.waffle > * > TR > :nth-child(109)"}, {"Column110", "TABLE.waffle > * > TR > :nth-child(110)"}, {"Column111", "TABLE.waffle > * > TR > :nth-child(111)"}, {"Column112", "TABLE.waffle > * > TR > :nth-child(112)"}, {"Column113", "TABLE.waffle > * > TR > :nth-child(113)"}, {"Column114", "TABLE.waffle > * > TR > :nth-child(114)"}, {"Column115", "TABLE.waffle > * > TR > :nth-child(115)"}, {"Column116", "TABLE.waffle > * > TR > :nth-child(116)"}, {"Column117", "TABLE.waffle > * > TR > :nth-child(117)"}, {"Column118", "TABLE.waffle > * > TR > :nth-child(118)"}, {"Column119", "TABLE.waffle > * > TR > :nth-child(119)"}, {"Column120", "TABLE.waffle > * > TR > :nth-child(120)"}, {"Column121", "TABLE.waffle > * > TR > :nth-child(121)"}, {"Column122", "TABLE.waffle > * > TR > :nth-child(122)"}, {"Column123", "TABLE.waffle > * > TR > :nth-child(123)"}, {"Column124", "TABLE.waffle > * > TR > :nth-child(124)"}, {"Column125", "TABLE.waffle > * > TR > :nth-child(125)"}, {"Column126", "TABLE.waffle > * > TR > :nth-child(126)"}, {"Column127", "TABLE.waffle > * > TR > :nth-child(127)"}, {"Column128", "TABLE.waffle > * > TR > :nth-child(128)"}, {"Column129", "TABLE.waffle > * > TR > :nth-child(129)"}, {"Column130", "TABLE.waffle > * > TR > :nth-child(130)"}, {"Column131", "TABLE.waffle > * > TR > :nth-child(131)"}, {"Column132", "TABLE.waffle > * > TR > :nth-child(132)"}, {"Column133", "TABLE.waffle > * > TR > :nth-child(133)"}, {"Column134", "TABLE.waffle > * > TR > :nth-child(134)"}, {"Column135", "TABLE.waffle > * > TR > :nth-child(135)"}, {"Column136", "TABLE.waffle > * > TR > :nth-child(136)"}, {"Column137", "TABLE.waffle > * > TR > :nth-child(137)"}, {"Column138", "TABLE.waffle > * > TR > :nth-child(138)"}, {"Column139", "TABLE.waffle > * > TR > :nth-child(139)"}, {"Column140", "TABLE.waffle > * > TR > :nth-child(140)"}, {"Column141", "TABLE.waffle > * > TR > :nth-child(141)"}, {"Column142", "TABLE.waffle > * > TR > :nth-child(142)"}}, [RowSelector="TABLE.waffle > * > TR"]),
    #"Linhas Principais Removidas" = Table.Skip(#"Tabela extraída de HTML",1),
    #"Colunas Removidas" = Table.RemoveColumns(#"Linhas Principais Removidas",{"Column1"}),
    #"Cabeçalhos Promovidos" = Table.PromoteHeaders(#"Colunas Removidas", [PromoteAllScalars=true]),
    #"Colunas Renomeadas" = Table.RenameColumns(#"Cabeçalhos Promovidos",{{"", "FUNDS CODE"}}),
    #"Índice Adicionado" = Table.AddIndexColumn(#"Colunas Renomeadas", "Índice", 1, 1, Int64.Type),
    #"Colunas Reordenadas" = Table.ReorderColumns(#"Índice Adicionado",{"Índice", "FUNDS CODE", "101425781", "101825381", "101824382", "101823383", "101343863", "101275931", "101733473", "101883323", "101730476", "101618588", "101585621", "101884322", "101617589", "101298908", "101853353", "101266940", "101569637", "101428778", "101260946", "101261945", "101567639", "101730476_1", "101618588_2", "101585621_3", "101884322_4", "101617589_5", "101298908_6", "101853353_7", "101266940_8", "101267939", "101628578", "101440766", "101435771", "101434772", "101527679", "101438768", "101266940_9", "101266940_10", "101824382_11", "101823383_12", "101343863_13", "101275931_14", "101733473_15", "101475731", "101233973", "101258948", "101311895", "101274932", "101865341", "101692514", "101284922", "101357849", "101694512", "101649557", "101384822", "101625581", "101325881", "101404802", "101638568", "101773433", "101301905", "101637569", "101698508", "101558648", "101762444", "101772434", "101770436", "101761445", "101771435", "101302904", "101755451", "101762444_16", "101772434_17", "101770436_18", "101585621_19", "101884322_20", "101617589_21", "101762444_22", "101462744", "101544662", "101334872", "101744462", "101538668", "101521685", "101465741", "101323883", "101683523", "101682524", "101820386", "101737469", "101742464", "101728478", "101489717", "101764442", "101606600", "101679527", "101607599", "101493713", "101504702", "101694512_23", "101649557_24", "101384822_25", "101625581_26", "101325881_27", "101404802_28", "101619587", "101811395", "101677529", "101664542", "101729477", "101665541", "101663543", "101622584", "101559647", "101794412", "101780426", "101825381_29", "101824382_30", "101823383_31", "101343863_32", "101275931_33", "101733473_34", "101883323_35", "101730476_36", "101794412_37", "101780426_38", "101730476_39", "101665541_40", "101663543_41", "101730476_42", "101260946_43", "101261945_44", "101262944", "101263943", "101264942", "101265941", "101266940_45", "101267939_46", "101268938", "101269937"}),
    #"Colunas Não Dinâmicas1" = Table.UnpivotOtherColumns(#"Colunas Reordenadas", {"Índice", "FUNDS CODE"}, "Atributo", "Valor"),
    #"Colunas Renomeadas1" = Table.RenameColumns(#"Colunas Não Dinâmicas1",{{"Índice", "Index"}})
in
    #"Colunas Renomeadas1"

I need to think a way of filtering simultaneously the rows and columns, for example, if I select three random funds, the Matrix will then show only the correlation between those 3 for me and so on

[v-janeyg-msft]

Hi, @datacauts 

I'm afraid not. If the records of each row are the same, the result on value will be automatically aggregated.

Best Regards,

Community Support Team _ Janey

[datacauts]

So I just have to add an ".1" or something like that in the end of one of the columns, isn't it right?

[v-janeyg-msft]

Hello, @datacauts 

As long as there is one column that can distinguish the difference, it's ok.

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey