RANKX - Calculated Column Instead of Measure

[vmonkey]

 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,

[v-huizhn-msft · Accepted solution]

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]

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]

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 

[vmonkey]

Ahh ... I see my problem ...  thank you Angela.  I was attempting to use slicers on the data which was giving weird results. I was not aware that they had no effect on the column calculation.  Makes sense.

My need for the column calculation was to help create a running total measure based on the order of [Count of Volume] as what I was doing was not working with the [clc_Rank] measure.  I'll post that question in a different thread.

thank you!