Forum Discussion
How to add header to a table?
a newbie question.
I have 3 tables, table1 [Client ID, Client Name]; tabel 2[Client ID, Role, Assigned Staff]; table 3 [Client ID, Gross Profit]
in table 2, Role has Customer service & Sales 2 types.
I would like to create a joint table in BT showing Client ID, Customer Service, Sales, Gross Profit
I normally use sumproduct in excel, not sure what should use in Power BI.
thank you.
In your sceanrio, how did you get GP = 2530? What's its calculate formula?
For other parts, you can achieve them in Query Edit. Please refer to following steps:
1. Go to Query Edit - > select Staff table - > on Home menu choose Merge Queries and merge table Client -> Expand Client table column Client Name
2. Like step 1, merge table Shipment too.
3. Choose column Role and go to Transform menu to select Pivot Column. Values column choose Staff and Aggregate funciton choose Maximum.
4. Close & Apply. Then you can see the GP will be summarized automatically. If you don't want to sum it, then you can create measure for it.
Thanks,
Xi Jin.
3 Replies
v-xjiin-msftSolution Sage
In your scenario, there’re 3 tables. And the relationship among these tables is that they all have a column [Client ID]. Right?
So when you want to join these 3 tables in Power BI desktop. You just need to create relationships for the three tables. And generally, when the three tables have a same column. Power BI desktop will automatically create relationships for them. And if it doesn’t create the relationships automatically. You can go to Modeling -> Manage Relationships to create by yourself.
Something like:
After creating the relationships, you can simply drag the columns you want to one single table.
Thanks,
Xi Jin.
thank you for your reply. pls see the tables and relationship I have.
and below is what I want to show in BI.
in Excel, I can use sumproduct to calculate the GP and use match&Index to find the CUS and SAL.not sure how to do it in BI.
thank you.
- v-xjiin-msft
In your sceanrio, how did you get GP = 2530? What's its calculate formula?
For other parts, you can achieve them in Query Edit. Please refer to following steps:
1. Go to Query Edit - > select Staff table - > on Home menu choose Merge Queries and merge table Client -> Expand Client table column Client Name
2. Like step 1, merge table Shipment too.
3. Choose column Role and go to Transform menu to select Pivot Column. Values column choose Staff and Aggregate funciton choose Maximum.
4. Close & Apply. Then you can see the GP will be summarized automatically. If you don't want to sum it, then you can create measure for it.
Thanks,
Xi Jin.
