Matrix issue

[TimK]

I am trying to achieve something in both PowerBI and Excel

Basically i have some data:

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

I have formatted the data and unpivoted the data in the years

The result i get in Excel is:

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

The issue is the rows for "Simon" are split out for each row of "Spent"

What i really want to achieve in PowerBI and Excel is a table that looks like this:

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

Could someone help me please?

Thanks

[BITomS · Accepted solution]

Hi @TimK ,

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.

Hope this helps.