Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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:
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
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
So I just have to add an ".1" or something like that in the end of one of the columns, isn't it right?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |