Forum Discussion
Calculated column to count records less than value in same column
Hello All,
I am struggling with something I thought was going to be simple enough, but hours later I need help!
I have the following dataset:
| Record | SE | M | R | Y | Percentile Rank | Normalised Percentile Rank |
| 1 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 2 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 3 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 4 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 5 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 6 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 7 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 8 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 9 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 10 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 11 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 12 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 13 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 14 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 15 | 6 | 9 | 15 | 24 | 68.75 | 100.00 |
| 16 | 5.96 | 8 | 1 | 24 | 35.42 | 42.86 |
| 17 | 5.95 | 7 | 1 | 24 | 31.25 | 35.71 |
| 18 | 5.94 | 5 | 2 | 24 | 25.00 | 25.00 |
| 19 | 5.94 | 5 | 2 | 24 | 25.00 | 25.00 |
| 20 | 5.93 | 0 | 5 | 24 | 10.42 | 0.00 |
| 21 | 5.93 | 0 | 5 | 24 | 10.42 | 0.00 |
| 22 | 5.93 | 0 | 5 | 24 | 10.42 | 0.00 |
| 23 | 5.93 | 0 | 5 | 24 | 10.42 | 0.00 |
| 24 | 5.93 | 0 | 5 | 24 | 10.42 | 0.00 |
In Excel I have formulas for M, R, Y, Percentile Rank & Normalisation
M = COUNTIF(B:B,"<"&B2) //counts all rows in the dataset < value
R = COUNTIF(B:B,"="&B2) // counts all rows in the dataset = value
Y =COUNT($A$2:$A$2235)
Percentile Rank = ((C2+(0.5*D2))/E2)*100
Normalised Rank = (F2-MIN(F:F))/((MAX(F:F)-MIN(F:F)))*100
I am really struggling to find the right formula in Power BI for M & R . I cant seem to get anything working.
Any help would be appreciated 🙂
10 Replies
Thanks so much parry2k and Ashish_Mathur , I am sooo very grateful for this.
Amazing and super quick!
You are welcome.
Anonymous althought Ashish_Mathur proposed calcualted columns, I would recommend to use measures for performance, but it all depends how big your dataset is.
Calculated column is the last thing you want to do. Cheers!!
Anonymous I think based on your sample dataset B2 = SE value 6 based on Record = 2, correct?
Hi parry2k , thanks yes cell B2 = 6,
There are 9 records less than 6 and 15 values equal to 6 but cant seem to find a powerbi formula that gets this value.
Anonymous here are measaures for you
B2 Value = CALCULATE ( MAX ( Table1[SE] ), Table1[Record] = 2 ) Count equal to B2 = VAR __b2 = [B2 Value] RETURN CALCULATE ( COUNTROWS ( Table1 ), Table1[Se] = __b2 ) Count less than B2 = VAR __b2 = [B2 Value] RETURN CALCULATE ( COUNTROWS ( Table1 ), Table1[Se] < __b2 )
