Forum Discussion
Issue with displaying multiple column values in a Matrix
Trying to replicate a pivot table from excel in Power BI. Shouldn't be difficult I thought, even though I'm pretty new to Power BI.
Issue is in the Column piece. I have two column that need to have their values shown but the Matrix is only showing one of them, the first in the list. Sample data is below.
My matrix is defined as :
Rows: CostCenter, CCName, Employee, EmployeeName
Columns: SBUOverhead, PorjPropOH
Value: Sum of MTD_Hours
So, if SBUOverhead is the first Column listed the matrix is showing me column header SBU and Overhead with 187 under SBU (which is correct) but it is not showing me column headers Project, Proposal, Overhead that go with ProjPropOH field and their totals. If I take out SBUOverhead and put in ProjPropOH (or rearrange them so it is 1st) - I get the three headers and totals where approriate. I want both sets of headers to display.
Any help, pointer, where to look, etc. is greatly appreciated.
| CostCenter | CCName | Employee | EmployeeName | MTD_Hours | SBUOverhead | ProjPropOH |
| 13553 | Marketing - Test | 11111 | John Doe | SBU | Overhead | |
| 13553 | Marketing - Test | 11111 | John Doe | 7 | SBU | Overhead |
| 13553 | Marketing - Test | 11111 | John Doe | 30 | SBU | Overhead |
| 13553 | Marketing - Test | 11111 | John Doe | SBU | Overhead | |
| 13553 | Marketing - Test | 11111 | John Doe | SBU | Overhead | |
| 13553 | Marketing - Test | 11111 | John Doe | 100 | SBU | Overhead |
| 13553 | Marketing - Test | 11111 | John Doe | 18 | SBU | Overhead |
| 13553 | Marketing - Test | 11111 | John Doe | Overhead | Overhead | |
| 13553 | Marketing - Test | 11111 | John Doe | Overhead | Overhead | |
| dd13553 | Marketing - Test | 11111 | John Doe | 32 | SBU | Project |
| 13553 | Marketing - Test | 11111 | John Doe | SBU | Project | |
| 13553 | Marketing - Test | 11111 | John Doe | SBU | Project | |
| 13553 | Marketing - Test | 11111 | John Doe | 155 | SBU | Proposal |
You could try merging the two columns into one so that the matrix isn't handling a nested structure.
CombinedColumn = [SBUOverhead] & " | " & [ProjPropOH]This can be a calculated column, so doesn't affect anything else.
Sweet - just check if you would need either of the individual columns or if the merged one alone would be sufficient so you don't cause yourself pain later on.
If you don't need anything else could you mark my response as an acceptable solution so other people don't spend time responding?
4 Replies
You could try merging the two columns into one so that the matrix isn't handling a nested structure.
CombinedColumn = [SBUOverhead] & " | " & [ProjPropOH]This can be a calculated column, so doesn't affect anything else.
Thanks. This gives me an idea to combine them prior to importing so I can make a set of unique combinations.
Sweet - just check if you would need either of the individual columns or if the merged one alone would be sufficient so you don't cause yourself pain later on.
If you don't need anything else could you mark my response as an acceptable solution so other people don't spend time responding?
