Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 Nor...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    6 years ago

    Hi,

    You may download my PBI file from here.

    Hope this helps.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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. 

parry2k's avatar
parry2k
Icon for Super User rankSuper User
6 years ago

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
)
  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous and output of these measures