Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

sum a column value based on another table column

I have a table1 with 2 columns A & B which has value like below 

A            B
p11
p13
p24
p76

 

Another table 2 with column A

A
p2
p3
p6
p8
p10

 

Both the tables are connected via dimension table table3 (union of table1 & Table2 )which looks like 

A
p1
p2
p3
p6
p7
p8
p10
p12

 

I wanted to sum the column B of table1 where column A of table1 is not matching with Column A of table 2 .

Desired O/P : (1+3+6)=10

I am using calculate with Filter using Values but not getting correct output .

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    After modification it works . Thanks Amitchandak .

    measure=
    var _tab = except(selectcolumns(Table1,"col",Table1[A]),selectcolumns(Table2,"col",Table2[a]))
    return
    calculate(sum(Table1[b], filter(Table1,Table1[A] in _tab))

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Try a measure like

     

    measure=
    var _tab = except(allselected(Table1[A]),allselected(Table1[b]))
    return
    calculate(countrows(Table1), filter(Table1,Table1[A] in _tab))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks for such a quick response , I made a little modification to make it work but the problem is it does not work properly when filter is applied 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        After modification it works . Thanks Amitchandak .

        measure=
        var _tab = except(selectcolumns(Table1,"col",Table1[A]),selectcolumns(Table2,"col",Table2[a]))
        return
        calculate(sum(Table1[b], filter(Table1,Table1[A] in _tab))