Forum Discussion

jeggen's avatar
jeggen
Icon for Helper II rankHelper 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!

     

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

jeggen paste some sample data and expected output. 

  • jeggen's avatar
    jeggen
    Icon for Helper II rankHelper 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
      Icon for Super User rankSuper 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
        Icon for Helper II rankHelper 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!