Forum Discussion

michaelsparrow's avatar
michaelsparrow
Helper I
9 years ago
Solved

New column - value depends on another column

 

Hi, I can't figure this one out.

I want to add a column to this table which equals: 120/Number of times a 'Staff Num' occurs in the 'Staff Number' Column.

So if 49974 occured 60 times in Staff Number column, the new column would have 2 in every row that 49974 was in.

 

Any ideas?

 

Cheers

Need Help
  • michaelsparrow's avatar
    michaelsparrow
    9 years ago

    the number is still about 100 times higher than the number i am looking for.

     

    That's ok, i'll just use constant lines.

     

    cheers

7 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Hi michaelsparrow

     

    Please try the following calculated column.  This formula assumes your table is called 'Table1'

     

    New Column = 
VAR StaffCol = 'Table1'[StaffNum]
VAR X = CALCULATE(COUNTROWS('Table1'),FILTER('Table1','Table1'[StaffNum] = StaffCol))
VAR Y = 120 
RETURN DIVIDE(x,y)

    This also treats your 120 as hardcoded.  If you'd like this to be dynamic and represent the number of rows in your table then let me know.

    Phil

    • michaelsparrow's avatar
      michaelsparrow
      Helper I
      9 years ago

      Hi,

       

      THanks a lot.

      Isn't working exactly right, but getting close i think. I've changed it a little bit as you can see below.

      However, the value in the 'New Column' is 50.44 whereas it should be a very small number. Looking to divide 280 by the number of times that MCFA shows up, then also the number of times CAT7, then also the number of times FAT7, all in the same column.

      Cheers

       

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi michaelsparrow

         

        Is it a simple case of swapping the order of the X and Y in the DIVIDE function?

         

        eg use DIVIDE(y,x) ?