Seeking help to create a ranking measure that doesnt change with selection in other tables

[tenzan]

Dear Power BI Community,

I am looking for assistance in finding a solution that calculates the vendor rankings while maintaining the same values, regardless of whether a specific vendor is selected in other visuals or not. Any help or guidance would be greatly appreciated.

Below is the DAX measure I have tried, which produces correct rankings when no vendor is selected but changes slightly (usually by 1) when a specific vendor is chosen:

Ranking Transaction=

IF (

    ISINSCOPE (Sheet1[_VENDOR_NAME] ),

    RANKX (

        ALL (Sheet1[_VENDOR_NAME] ),

        CALCULATE ( SUM (Sheet1[_TRANSACTION_VALUE] ) ),

        ,

        DESC,

        DENSE

    )

)

Visual example: before nothing is selected

	New York	London	Tokyo
Vendor A	1	3	3
Vendor B	2	2	2
Vendor C	3	1	1

Ideally, when vendor C is selected in other visuals or from the slicer, the table should produce

	New York	London	Tokyo
Vendor C	3	1	1

But instead, i get something like this, which is slighly off by 1 for both London and Tokyo. 

	New York	London	Tokyo
Vendor C	3	2	2

[tenzan]

Has anyone else encountered a similar issue and found a solution to this? Given the popularity of the ranking measure, i believe there must be someone out ther who can provide guidance. Your assitance would be greaetly appreciated. 

[amitchandak]

@tenzan , Try like

RANKX (
filter(ALL (Sheet1[_VENDOR_NAME],Sheet1[_CITY] ),Sheet1[_CITY] = max(Sheet1[_CITY]))
CALCULATE ( SUM (Sheet1[_TRANSACTION_VALUE] ) ),
,
DESC,
DENSE
)

[tenzan]

I appreciate your time and effort in addressing my concern.

Regrettably, after implementing the DAX query you provided, the issue persists. We continue to see a discrepancy in our table rankings - specifically, an 'off by 1' error for certain countries when a vendor is selected in other visuals.

Despite extensive online research and numerous attempts to resolve this over the past six months, we haven't yet found a successful solution. Considering the importance of data accuracy in our work, this has become a pressing issue.

We are still eager to find a solution or an alternative approach to this problem. If you or anyone else could provide further assistance, it would be greatly appreciated.

Thank you once again for your initial help.

Before 

After clicking a vendor name in other visuals

[amitchandak]

@tenzan , Try

RANKX (
filter(Summarize(ALL(Sheet1), Sheet1[_VENDOR_NAME],Sheet1[_CITY] ),Sheet1[_CITY] = max(Sheet1[_CITY]))
CALCULATE ( SUM (Sheet1[_TRANSACTION_VALUE] ) ),
,
DESC,
DENSE
)

[tenzan]

@amitchandak thank you again for checking.

It seems the issue still persists with the new measure using summarize function.

Before

After clicking a vendor name in other visuals