Forum Discussion

jeggen's avatar
jeggen
Helper II
6 years ago
Solved

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 ke...
Need Help
  • parry2k's avatar
    parry2k
    6 years ago

    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!

     

jeggen's avatar
jeggen
Helper II
6 years ago

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    
DateClientEmployeeContract AmountProduct
1/1/2020ABC IncJoe$10,000Wood
3/1/2020XYZ IncMary$50,000Metal
4/5/2020LMN InkSam$5,000Plastic
     
Interactions   
DateClientEmployeeSummaryStatus
2/15/2020ABC IncJoeCalled to thank for orderComplete
3/12/2020LMN IncSamCalled to ask about contractComplete
5/15/2020123 IncMaryRemember to check on ContractPending
     
     
Payments    
DateClientEmployeeAmount 
1/1/2020ABC IncJoe10,000 
1/25/2020XYZ IncMary15,000 
     
     
     
Summary Table   
DateClientEmployeeDetailsSource Table
1/1/2020ABC IncJoe10,000Payments
1/25/2020XYZ IncMary15,000Payments
2/15/2020ABC IncJoeCalled to thank for orderInteractions
3/12/2020LMN IncSamCalled to ask about contractInteractions
5/15/2020123 IncMaryRemember to check on ContractInteractions
1/1/2020ABC IncJoe$10,000Contracts
3/1/2020XYZ IncMary$50,000Contracts
4/5/2020LMN InkSam$5,000Contracts
parry2k's avatar
parry2k
Super User
6 years ago

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!

 

  • jeggen's avatar
    jeggen
    Helper II
    6 years ago

    Purpose is to integrate a couple of different tables into a high level summary and include them all in a calendar view. This worked perfect!