Rank

[Anonymous]

Hi

I am having one column in table named [MeasureId] and [Earnings] is the Calculated measure. I have calculated Rank of Earning using RANKX function but for last 5 rows [Earnings] is same and that's why Rank for those rows is coming out to be same. Is there any way to showo Rank after 6th rows as 7,8,9,10

[Anonymous]

@Anonymous,

RANKX will return the same ranking for the same value, this is by design and it is the correct behavior.

In your scenario, please use the following DAX to work around this issue.

1.Create a summarized table using DAX below.

Table = SUMMARIZE(Table1,Table1[Measure[MeasureId]]],"NewEarning",[Earnings])

2. Then create a column using DAX below in the new table.

Rank = 
RANKX ('Table',
    RANKX ('Table',
        'Table'[NewEarning],
        ,
        ASC
    )
        + DIVIDE (
            RANKX (
                    'Table','Table'[Measure[MeasureId]]],
                ,
                ASC
            ),
            
                COUNTROWS (
                    
                        'Table'
                    )
                
                    + 1
            )
        )

Regards,
Lydia

[Anonymous]

Hi Lydia

I am confused anout this "NewEarning". I do not have this in my database. Can you please elaborate ho wit will work.

[Anonymous]

@Anonymous,

It is a column in the new table, you would need to firstly create a summarized table using DAX below.

Table = SUMMARIZE(Table1,Table1[Measure[MeasureId]]],"NewEarning",[Earnings])


Regards,
Lydia

[Anonymous]

Thanks Lydia for your response !!

I want to create as as Measure and not column so that It can be calculated in runtime considering slicers.

[Anonymous]

@Anonymous,

What field would you like to create slicer?

Regards,
Lydia

[Anonymous]

I mean to say as per Slicers Year, Client, the number of rows coming from table may vary. so for example if I select Client = 23 and Year = 2018 from slicer then, there could be only 5 'Measure'[MeasureId] may return whereas if I select Client=24 and Year=2017 then say may be 4 rows with 'Measure'[MeasureID] will return. And that's why I want to create Measure instead of column.

[Anonymous]

@Anonymous,

It seems that the table 'Measure'[MeasureID] locates in is dynamic, I doubt if it is possible to create a rank measure in this case. Could  you please share sample data of the table that includes year and client fields?

Regards,
Lydia