Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
Just change the last part of the formula from Divide(X,Y) to Divide(Y,X)
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)
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |