Forum Discussion
Combining Data into Summary Table
All,
I'm looking for some help on how to combine some data into a summary table. The main reason is to be able to map it all out into a calendar visualization and then click on day(s) and view key activities from those days. I am looking at combining really just four fields from 2+ tables into a summary table that includes these fields, a column with the source table listed. Three of the fields would always be the same (date, client, employee), the third would vary by table but would always just be text, and the fourth field would record the source table for the summary data which I want so I can filter by activity type.
Here might be an example of the source tables and output table:
- Contracts
- Date
- Client
- Employee
- Contract Amount (specific to this table)
- Interactions
- Date
- Client
- Employee
- Summary (specific to this table)
- Payments
- Date
- Client
- Employee
- Amounts (specifc to this table)
- Summary Table:
- Date
- Client
- Employee
- Details (Contract Amount, Summary, Amount)
- Source Table (values would be contracts, interactions, payments,)
jeggen don't know the purpose of this but here it is, add the following expression as calculated table
Summary = UNION ( SELECTCOLUMNS ( Contract, "Date", Contract[Date], "Client", Contract[Client], "Employee", Contract[Employee], "Details", FORMAT ( Contract[Contract Amount], "General Number" ), "Source Table", "Contract" ), SELECTCOLUMNS ( Interactions, "Date", Interactions[Date], "Client", Interactions[Client], "Employee", Interactions[Employee], "Details", Interactions[Summary], "Source Table", "Interactions" ), SELECTCOLUMNS ( Payments, "Date", Payments[Date], "Client", Payments[Client], "Employee", Payments[Employee], "Details", FORMAT ( Payments[Amount], "General Number" ), "Source Table", "Payments" ) )this is where you add the above expression
I would💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
4 Replies
Here's some sample tables in Excel: Note that all of the tables have more data than I need in the summary table, so it's not just as simple as merging all the data and also need to create the "source table" column, which tells me what the entry is.
Contracts Date Client Employee Contract Amount Product 1/1/2020 ABC Inc Joe $10,000 Wood 3/1/2020 XYZ Inc Mary $50,000 Metal 4/5/2020 LMN Ink Sam $5,000 Plastic Interactions Date Client Employee Summary Status 2/15/2020 ABC Inc Joe Called to thank for order Complete 3/12/2020 LMN Inc Sam Called to ask about contract Complete 5/15/2020 123 Inc Mary Remember to check on Contract Pending Payments Date Client Employee Amount 1/1/2020 ABC Inc Joe 10,000 1/25/2020 XYZ Inc Mary 15,000 Summary Table Date Client Employee Details Source Table 1/1/2020 ABC Inc Joe 10,000 Payments 1/25/2020 XYZ Inc Mary 15,000 Payments 2/15/2020 ABC Inc Joe Called to thank for order Interactions 3/12/2020 LMN Inc Sam Called to ask about contract Interactions 5/15/2020 123 Inc Mary Remember to check on Contract Interactions 1/1/2020 ABC Inc Joe $10,000 Contracts 3/1/2020 XYZ Inc Mary $50,000 Contracts 4/5/2020 LMN Ink Sam $5,000 Contracts jeggen don't know the purpose of this but here it is, add the following expression as calculated table
Summary = UNION ( SELECTCOLUMNS ( Contract, "Date", Contract[Date], "Client", Contract[Client], "Employee", Contract[Employee], "Details", FORMAT ( Contract[Contract Amount], "General Number" ), "Source Table", "Contract" ), SELECTCOLUMNS ( Interactions, "Date", Interactions[Date], "Client", Interactions[Client], "Employee", Interactions[Employee], "Details", Interactions[Summary], "Source Table", "Interactions" ), SELECTCOLUMNS ( Payments, "Date", Payments[Date], "Client", Payments[Client], "Employee", Payments[Employee], "Details", FORMAT ( Payments[Amount], "General Number" ), "Source Table", "Payments" ) )this is where you add the above expression
I would💖 Kudos 🙂 if my solution helped. If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
