Showing correct rank in card visual

[AyushAwasthi]

I have a table visual with the columns: Company, Revenues, Ratio and HQ (source table - "Final Data"). I also have a slicer on this page which takes its values from a disconnected table (source table - "List Data") with only the company names, which I require because I don't want to filter the table visual, based on that selection and it has some other functionality as well. The names of the companies match in both the data tables.

Now I want to show the rank of the company that is selected in the slicer, based on the ratio (as the company in the slicer would be in the table visual as well). I already have a measure created for ranking which is defined as:

All Rank =
RANKX(ALLSELECTED('Final Data'),CALCULATE(SUM('Final Data'[Audit fees as % of Rev CY])),,DESC,Dense)

I want to show the rank in a card visual in the format "5/345", if a company is 5th in a list of 345 companies. The table visual is also influenced by a revenue slicer on the page and some page level filters. How to achieve this?
Sample data:

Company	Revenues	Ratio	HQ	All Rank
ABC	3457	0.56%	XYZ	2
DEF	2424	0.58%	SDWSC	1
GHI	234823	0.12%	SCBJS	5
JKL	237	0.45%	DSB	3
MNO	32874324	0.39%	SJCB	4

Now if the user selects JKL in the slicer (from List Data) the card visual should show "3/5". The table should not be filtered. I just want the rank value.

Thanks in advance!

[AyushAwasthi]

Thanks a lot. Works exactly as needed!

[Nasif_Azam · Accepted solution]

Hey @AyushAwasthi ,

To show the correct rank of the selected company in a card visual in the format 3/5 without filtering the table visual, while accounting for other filters like revenue slicers and page-level filters.

Let:

• Final Data = main table with Company, Revenues, Ratio, etc.
• List Data = disconnected slicer table with Company names
• You already have a slicer from List Data[Company]
• You want to use Ratio (or Audit fees as % of Rev CY) to rank companies

DAX for Rank Display in Card Visual:

Selected Company Rank =
VAR SelectedCompany =
SELECTEDVALUE ( 'List Data'[Company] )

VAR RankedTable =
ADDCOLUMNS (
FILTER (
ALLSELECTED ( 'Final Data' ),
NOT ISBLANK ( 'Final Data'[Ratio] )
),
"RankValue", RANKX (
ALLSELECTED ( 'Final Data' ),
CALCULATE ( SUM ( 'Final Data'[Ratio] ) ),
,
DESC,
DENSE
)
)

VAR SelectedCompanyRank =
MAXX (
FILTER ( RankedTable, 'Final Data'[Company] = SelectedCompany ),
[RankValue]
)

VAR TotalCompanies =
COUNTROWS ( RankedTable )

RETURN
IF (
NOT ISBLANK ( SelectedCompanyRank ),
SelectedCompanyRank & "/" & TotalCompanies,
BLANK ()
)

If the company JKL is selected in slicer and ranks 3rd out of 5 based on filtered context → card shows "3/5".

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam