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
