Forum Discussion
Sum rows to append to another table
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!
3 Replies
- mwegener
Most Valuable Professional
Hi Anonymous ,
develop a star schema in which you can filter both tables using shared master data tables.
https://docs.microsoft.com/en-us/power-bi/guidance/star-schema
- Fowmy
Super User
Anonymous
You need to first transform the manually entered data into a format that will align with the existing 1st table. Then, union these tables to create a combined table.
Where do you find it challenging, at which stage?________________________
Did I answer your question? Mark this post as a solution, this will help others!.
I accept KUDOS 🙂
- v-easonf-msft
Community Support
Hi， Anonymous
The general process is as said by Fowmy .
However , I'm still not clear about the structure of your second report.
Can you display your report and result with specific fields and data rather than "0","CATEGORY OF WORK e.g. Processing" ,"Sub category e.g. Preparation".
It will better help us to transform your data.
You may check if this related post help.
https://community.powerbi.com/t5/Desktop/Sum-data-to-new-rows-in-same-table/m-p/885031
Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
