Forum Discussion
New column - value depends on another column
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
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
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
Is it a simple case of swapping the order of the X and Y in the DIVIDE function?
eg use DIVIDE(y,x) ?
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
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)
Just change the last part of the formula from Divide(X,Y) to Divide(Y,X)
