New column - value depends on another column

[michaelsparrow]

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

[Phil_Seamark]

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]

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

[Mi2n]

Just change the last part of the formula from Divide(X,Y) to Divide(Y,X)

[Phil_Seamark]

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) ?

[michaelsparrow]

Ok thanks, close but still not the values i'm after. I think i might be going about it the wrong way.

To get the values I'm looking for I need to do the following:

120 / (The number of times "MCFA" occurs in the 'resource' column and at the same time "SICK" occurs in the 'detail' column)

120 / (The number of times "CAP" occurs in the 'resource' column and at the same time "SICK" occurs in the 'detail' column)

120 / (The number of times "FO" occurs in the 'resource' column and at the same time "SICK" occurs in the 'detail' column)

Ideally want these 3 things in the same Measure.

Thanks a lot

[Phil_Seamark]

Could this be the tweak?

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

[michaelsparrow · Accepted solution]

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