topic Re: RANKX producing false ties and skipping ranks in DAX Commands and Tips

RANKX producing false ties and skipping ranks

lava_ — Thu, 22 Dec 2022 19:21:28 GMT

Hi there,

I have been searching for a couple of days for an answer on this, but haven't been able to get anywhere. Also dealing with time constraints.

I'm trying to rank vendors within the sector's subgroup in a matrix, and it's producing false ties and skipping values (see screenshot below). I'm using two measures to determine the rank: 1) a measure that finds the average value and 2) a measure that determines the vendor's rank based on that average. The ranking is based on the vendor that has a value closest to zero.

There is a "Difference" column in our data table that finds the difference between trade price and vendor price and a "Abs Difference" column that determines the absolute value of that difference. The two columns in the matrix show the average of that difference, which are created by measures, one of which is below.

In the screenshot above, looking at the "Average Abs Diff" column, I first created a measure to find the average:

Average Abs Diff = AVERAGEX(Sheet1, Sheet1[Abs Difference])

To determine the rank, I created the following measure:

Rank_Avg Abs Diff =

IF(

ISINSCOPE(Sheet1[Vendor]),

RANKX(

CALCULATETABLE(

VALUES(Sheet1[Vendor]),

ALLSELECTED(Sheet1[Vendor])

[Average Abs Diff],

ASC

)

Am I using the correct combination of functions?

Re: RANKX producing false ties and skipping ranks

amitchandak — Fri, 23 Dec 2022 04:16:46 GMT

lava_ , I think the new offset function can help here

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

Re: RANKX producing false ties and skipping ranks

Anonymous — Fri, 23 Dec 2022 05:44:14 GMT

Hi lava_ ，

Please update the formula of measure [Rank_Avg Abs Diff] as below and check if that is what you want. Please find the details in the attachment.

Rank_Avg Abs Diff = VAR _sector = SELECTEDVALUE ( 'Sheet1'[Sector] ) VAR _subgroup = SELECTEDVALUE ( 'Sheet1'[Sub Group] ) RETURN IF ( ISINSCOPE ( Sheet1[Vendor] ), RANKX ( FILTER ( ALLSELECTED ( Sheet1 ), 'Sheet1'[Sector] = _sector && 'Sheet1'[Sub Group] = _subgroup ), CALCULATE ( [Average Abs Diff] ), , ASC, DENSE ) )

If the above one can't help you get the desired result, please provide some raw data in your table 'Sheet1' (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards