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.

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

Anonymous I think based on your sample dataset B2 = SE value 6 based on Record = 2, correct?

  • 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