Matrix issue

Question

I am trying to achieve something in both PowerBI and Excel&nbsp;Basically i have some data:Sales PersonProductSpent2023202420252026SimonOne&nbsp;&nbsp;&nbsp;&nbsp; 2,500.00&nbsp;&nbsp;&nbsp;&nbsp; 2,125.00&nbsp;&nbsp;&nbsp;&nbsp; 2,125.00&nbsp;&nbsp;&nbsp;&nbsp; 2,125.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 452.00SimonTwo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 125.00&nbsp;&nbsp;&nbsp;&nbsp; 2,215.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 215.00&nbsp;JohnOne&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,203.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 225.00&nbsp;&nbsp;JohnTwo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,335.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,024.00&nbsp;JohnThree&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 112.00&nbsp;&nbsp;&nbsp;&nbsp; 3,225.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,125.00PeterOne&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,025.00&nbsp;&nbsp;&nbsp;&nbsp; 3,354.00&nbsp;&nbsp;&nbsp;&nbsp; 3,220.00&nbsp;&nbsp;&nbsp;&nbsp; 2,152.00PeterTwo&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 253.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 252.00&nbsp;&nbsp;&nbsp;I have formatted the data and unpivoted the data in the years&nbsp;The result i get in Excel is:Sales PersonSpent2023202420252026Grand TotalJohn(blank)4,650.003,450.002,024.002,125.0012,249.00Peter(blank)2,278.003,606.003,220.002,152.0011,256.00Simon1252,215.00&nbsp;215.00&nbsp;2,430.00Simon25002,125.002,125.002,125.00452.006,827.00Grand Total&nbsp;11,268.009,181.007,584.004,729.0032,762.00&nbsp;The issue is the rows for "Simon" are split out for each row of "Spent"&nbsp;What i really want to achieve in PowerBI and Excel is a table that looks like this:Sales PersonSpent2023202420252026Grand TotalJohn&nbsp;(blank)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,650.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,450.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,024.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,125.00&nbsp;&nbsp;&nbsp; 12,249.00Peter&nbsp;(blank)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,278.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,606.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 3,220.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,152.00&nbsp;&nbsp;&nbsp; 11,256.00Simon&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,625.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,340.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,125.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,340.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 452.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,257.00Grand Total&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 2,625.00&nbsp;&nbsp;&nbsp; 11,268.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 9,181.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 7,584.00&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 4,729.00&nbsp;&nbsp;&nbsp; 32,762.00&nbsp;Could someone help me please?&nbsp;Thanks

bitoms · Accepted Answer

Hi&nbsp;TimK&nbsp;,&nbsp;From your description it sounds like you have just missed the transformation to unpivot the 'spent' column as well as the years? Just apply the same transformation logic you are using for years to the 'spent' column.&nbsp;Hope this helps.&nbsp;&nbsp;

Forum Discussion

Matrix issue

1 Reply

Related content

Matrix Visual Sorting Issue

Matrix calculation Issue - DAX

Matrix visual issue !!

Matrix alignment issue

Averagex issue

Sales Person	Product	Spent	2023	2024	2025	2026
Simon	One	2,500.00	2,125.00	2,125.00	2,125.00	452.00
Simon	Two	125.00	2,215.00		215.00
John	One		2,203.00	225.00
John	Two		2,335.00		2,024.00
John	Three		112.00	3,225.00		2,125.00
Peter	One		2,025.00	3,354.00	3,220.00	2,152.00
Peter	Two		253.00	252.00

Sales Person	Spent	2023	2024	2025	2026	Grand Total
John	(blank)	4,650.00	3,450.00	2,024.00	2,125.00	12,249.00
Peter	(blank)	2,278.00	3,606.00	3,220.00	2,152.00	11,256.00
Simon	125	2,215.00		215.00		2,430.00
Simon	2500	2,125.00	2,125.00	2,125.00	452.00	6,827.00
Grand Total		11,268.00	9,181.00	7,584.00	4,729.00	32,762.00

Sales Person	Spent	2023	2024	2025	2026	Grand Total
John	(blank)	4,650.00	3,450.00	2,024.00	2,125.00	12,249.00
Peter	(blank)	2,278.00	3,606.00	3,220.00	2,152.00	11,256.00
Simon	2,625.00	4,340.00	2,125.00	2,340.00	452.00	9,257.00
Grand Total	2,625.00	11,268.00	9,181.00	7,584.00	4,729.00	32,762.00