Forum Discussion

sbharti123's avatar
sbharti123
Frequent Visitor
7 years ago
Solved

Aggregating columns in each row having null values

Hi All,   I need a small help. I have a table that looks like below:   NAME ID1 ID2 ID3 A 1 null 9 B 2 3 5 C 3 null 5   Now, I need to create a calculated column, whi...
Need Help
  • sbharti123's avatar
    sbharti123
    7 years ago

    Thanks for your quick guide. I managed to get it right as below:

     

    Avrg =
    VAR summ = (table1[id1]+ table1[id2] + table1[id3])
    VAR cnt = CALCULATE(COUNTAX(table1,[id1]))+ CALCULATE(COUNTAX (table1,[id2])) + CALCULATE(COUNTAX(table1,[id3]))
    return DIVIDE(summ,cnt)
jthomson's avatar
jthomson
Solution Sage
7 years ago

The best option would be to do some manipulation of your data so you just have name, id number and value as columns and then use a matrix, but a simple sum of each column divided by a counta of each column ought to work as well

  • sbharti123's avatar
    sbharti123
    Frequent Visitor
    7 years ago

    Thanks for your quick guide. I managed to get it right as below:

     

    Avrg =
    VAR summ = (table1[id1]+ table1[id2] + table1[id3])
    VAR cnt = CALCULATE(COUNTAX(table1,[id1]))+ CALCULATE(COUNTAX (table1,[id2])) + CALCULATE(COUNTAX(table1,[id3]))
    return DIVIDE(summ,cnt)