Forum Discussion

vmonkey's avatar
vmonkey
Icon for Helper I rankHelper I
8 years ago
Solved

RANKX - Calculated Column Instead of Measure

 I have some binned data that I am ranking by the count.  I have created a measure that ranks the count by descending. The table looks like this:

 

 

The code for the measure is as follows:

 

clc_Rank = RANKX(ALL('vmFNC_DATA_DAILY_RTH()'[Volume (bins)]),CALCULATE(COUNTA([Volume])))

 

When I try and use the above code in a column, PowerBI complains of a circular dependancy.

 

Is it possible to have a calculated column to perform the rank rather than the measure, and if so, what would the code look like?

 

thank you,

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    8 years ago

    Hi vmonkey,

    For your scenario, you can create the calculated column using the formula below.

    Rank =
RANKX (
    'vmFNC_DATA_DAILY_RTH()',
    CALCULATE (
        COUNTA ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ),
        ALLEXCEPT ( 'vmFNC_DATA_DAILY_RTH()', 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] )
    ),
    ,
    DESC,
    DENSE
)


    If you want to use measure to get the rank, please try the following formula, and check if it works fine.

    clc_Rank =
RANKX (
    'vmFNC_DATA_DAILY_RTH()',
    CALCULATE (
        COUNTA ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ),
        ALLEXCEPT ( 'vmFNC_DATA_DAILY_RTH()', 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] )
    ),
    ,
    DESC
)

    Please feel free to ask if you have other issue.

    Best Regards,
    Angelia

     

4 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi vmonkey,

    For your scenario, you can create the calculated column using the formula below.

    Rank =
RANKX (
    'vmFNC_DATA_DAILY_RTH()',
    CALCULATE (
        COUNTA ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ),
        ALLEXCEPT ( 'vmFNC_DATA_DAILY_RTH()', 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] )
    ),
    ,
    DESC,
    DENSE
)


    If you want to use measure to get the rank, please try the following formula, and check if it works fine.

    clc_Rank =
RANKX (
    'vmFNC_DATA_DAILY_RTH()',
    CALCULATE (
        COUNTA ( 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] ),
        ALLEXCEPT ( 'vmFNC_DATA_DAILY_RTH()', 'vmFNC_DATA_DAILY_RTH()'[Volume (bins)] )
    ),
    ,
    DESC
)

    Please feel free to ask if you have other issue.

    Best Regards,
    Angelia

     

    • vmonkey's avatar
      vmonkey
      Icon for Helper I rankHelper I
      8 years ago

      Thank you for your help v-huizhn-msft,

       

      With the new column code provided in your post, the resulting table looks as follows:

       

       

      The current code that I am using for the measure (as seen in my first post) is working perfectly.  I am trying to get it to work as a column calculated field rather than a measure.

       

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi vmonkey,

         

        In the resource table, the rank works fine, right? If it is, please "don't summarize" when you select the calculated column in table visual.

        Best Regards,
        Angelia