Forum Discussion

wneiton's avatar
wneiton
Icon for Resolver I rankResolver I
8 years ago
Solved

Create a new table from sum columns from existing tables

Help, please 

I need create a new table from another two or more table. 

e.g. 

I have two independent tables (no relationships) in Power BI. Each table has a column with values, and I need to sum theses values and create another table with the result of this sum. example below

 

 

 

thanks!!

 

Need Help
  • wneiton's avatar
    wneiton
    8 years ago

    Hi, Ashish_Mathur Greg_Deckler

     

    I have solved my problem using two functions: 

    DISTINCT and SWITCH. 

     

    1 - I've combined all my tables using append queries, created Append2 Table. 

     

    2 - Second I have created new table. 

    Criterios = DISTINCT(Append2[Attribute]) and 

    valores = SWITCH(Criterios[Attribute];"impacto1";[Media_Impacto1];"impacto2";[Media_Impacto2];"impacto3";[Media_Impacto3];"visao1";[Media_Visao];"visao2";[Media_Visao2];"visao3";[Media_Visao3])

     

    3  - Then I've pulled Attribute and valores to the visual, now I can order by valores(value)

     

    ps: Media_Impacto is a previous measure that I had created for each table.

     

    Wneiton

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Hmm, I would create a table using enter data that was just a single column with the text "table01" and "table02" in it as rows. Then you could create a calculated column like:

     

    Column = IF([Column]="table01",SUMX('Table1'[Column]),SUMX('Table2'[Column]))
    • wneiton's avatar
      wneiton
      Icon for Resolver I rankResolver I
      8 years ago

      hi Greg_Deckler Ashish_Mathur

      Yours example, helped me, thanks, but here's my true reality.

      I need to do an arithmetic sum of all impacto1 values and create a new table as a result. e.g

       

       

      New_table

      Column     |    values 

      impacto1        1,6 

      impacto2        2,3 

      impacto3        2,8

      ....

      impacto20      1,5 

       

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi,

         

        Drag the Attribute column to the row labels of the Table visual and filter it on "Begins with" Impacto.  Write this measure

         

        =SUM(Data[Value])

         

        Hope this helps.