Forum Discussion

vadepu's avatar
vadepu
Frequent Visitor
5 years ago
Solved

Add two columns values from different tables

Hi , 

 

Need help !!

 

I have below tables

Table 1:

 

Table 2:

 

Table 3:

 

Note: Score column is based on some condition pn Age column in all the tables.

Example:

score = IF((Table1[Age])<>0,IF((table1[Age])>45,100+(Table1[Age])/10,0),50)
 
i want sum all the score columns and result should be as below.
 

 

Thanks

 

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi  vadepu ,

    Here are the steps you can follow：

    1. Create calculated table.

    calculatedtable1 =
var _1=SUMMARIZE('Table1',[Brand],"Score",MIN('Table1'[Score]))
var _2=SUMMARIZE('Table2',[Brand],"Score",MIN('Table2'[Score]))
var _3=SUMMARIZE('Table3',[Brand],"Score",MIN('Table3'[Score]))
var _union=UNION(_1,_2,_3)
return
_union
    calculatedtable2 =
SUMMARIZE('calculatedtable1',[Brand],"Score",SUM('calculatedtable1'[Score]))

    2. Create calculated column.

    Car = RANKX('calculatedtable2','calculatedtable2'[Brand],,ASC)

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    vadepu , based on what I got

    A new column in table A

     

    new column = maxx(filter(Table3, Table1[Brand] = Table1[Brand]), Table3[Score]) + maxx(filter(Table2, Table1[Brand] = Table1[Brand]), Table2[Score])
    + Minx(filter(Table1, Table1[Brand] = earlier(Table1[Brand])), Table1[Score])

    • vadepu's avatar
      vadepu
      Frequent Visitor
      5 years ago

      Hi amitchandak , thank you for your reply... i just want to add score columns of all the three tables and show in the table view just car and total score

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        vadepu , While the above should help as a new column in tbale one. With common dimension brand

         

        you should able to get like

        Sum(Table1[Score]) + sum(Table2[Score]) + sum(Table3[Score])

         

        or

        Minx(filter(allselected(Table1), Table1[Brand] = Max(Table1[Brand])), Table1[Score]) +  sum(Table2[Score]) + sum(Table3[Score])

         

        Display with common brand and Car from Table 1

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  vadepu ,

    Here are the steps you can follow：

    1. Create calculated table.

    calculatedtable1 =
var _1=SUMMARIZE('Table1',[Brand],"Score",MIN('Table1'[Score]))
var _2=SUMMARIZE('Table2',[Brand],"Score",MIN('Table2'[Score]))
var _3=SUMMARIZE('Table3',[Brand],"Score",MIN('Table3'[Score]))
var _union=UNION(_1,_2,_3)
return
_union
    calculatedtable2 =
SUMMARIZE('calculatedtable1',[Brand],"Score",SUM('calculatedtable1'[Score]))

    2. Create calculated column.

    Car = RANKX('calculatedtable2','calculatedtable2'[Brand],,ASC)

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly