Hi all,
Hoping I can get some help with this, fairly new, still learning - hopefully I have given enough detail.
I am trying to add a column into my dataset which would sum the entire row as opposed to the column. The reason I am doing this is because I want two different reports to be merged as one (I have created a dummy table below for reference) - In an ideal world, both sources would have used the same format of data entry, but alas, why make life easy.
In table 1 - The set up is as follows:
| CLIENT | TEAM/LOCATION | WORKTYPE | HOURS |
| ABC Ltd | UK | Management | 1 |
| 123 Ltd | UK | Admin | 0.5 |
| XYZ LLP | US | Management | 0.1 |
| 246 Plc | US | Admin | 3 |
and it then feeds into a matrix and slicer like so
Slicer: Client
Matrix:
| WORKTYPE | UK | US | TOTAL |
| Management | 1 | 0.1 | 1.1 |
| Admin | 0.5 | 3 | 3.5 |
| Total | 1.5 | 3.1 | 4.6 |
The first report is an automated one pulled from a system, so all the relevant manipulation and sorting has been done in Power Bi. The second report however is one that is manually updated by a team and has has been set up like so (0 in place of hours that will be manually added in) -
| CLIENT NAME | CATEGORY OF WORK e.g. Processing | CATEGORY OF WORK | CATEGORY OF WORK |
| | HOURS | HOURS | HOURS |
| | Sub category e.g. Preparation | WORKTYPE | WORKTYPE |
| Management | 0 | 0 | 0 |
| Admin | 0 | 0 | 0 |
| Other worktypes | 0 | 0 | 0 |
| | | | |
I've managed to add 2 columns at the end to pull the client name and manually add in a team/location so the table looks like the above with the two additional columns with repeating client names and team/location.
I want to ideally somehow get the relevant information from this sheet across and have it feed into the first report so that my visualisation/matrix looks like this:
| WORKTYPE | UK | US | New location | TOTAL |
| Management | 1 | 0.1 | 0 | 1.1 |
| Admin | 0.5 | 3 | 0 | 3.5 |
| Other Worktype | 0 | 0 | 0 | 0 |
| Total | 1.5 | 3.1 | 0 | 4.6 |
Hopefully the above makes sense, thanks again!
