RANKX producing false ties and skipping ranks

[lava_]

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? 

[v-yiruan-msft · Accepted solution]

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

[amitchandak]

@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