show rank in cards (without any row)

[logan_logan]

Hi I want to show rank in cards.

Like 

Category     SALES

A                  22

B                  29

C                  50               

D                  60

So when I select category C, then in the card I should see '2' as the rank on sales is the second.

I treid this DAX that works in table/matrix but not on Cards.

Rank_LM = RANKX(ALL('table'[Categoy]),sum(SALES),,DESC)

[Anonymous · Accepted solution]

Hi @logan_logan ,

Please try to create a measure with below dax formula:

Measure2 =
VAR _rk =
    RANKX ( ALL ( 'Table' ), CALCULATE ( MAX ( 'Table'[SALES] ) ),, DESC, DENSE )
VAR _result =
    IF ( ISFILTERED ( 'Table'[Category] ), _rk, BLANK () )
RETURN
    _result

Please refer the attached .pbix file.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[logan_logan]

I had to tweak a lillte bit as I had two categories but **bleep** worked. Thanks.

[DallasBaba]

@logan_logan you can reshape with a CALCULATE function:

Rank_LM_Category2 =
RANKX(
FILTER('table', 'table'[Category] = "Category 2"),
CALCULATE(SUM('table'[Sales]), ALL('table'[Category])),
,
DESC
)

Let me know if this works

Thanks

[logan_logan]

Hi,

Actually I will filter another category(lets say caterogy 2) and then I need the rank on category (A,B,C,D). 

So can you please reshape your dax?

[DallasBaba]

@logan_logan let try FILTER constent to your meassure for the table to include only the rows where the Category matches the selected category in your card.

Rank_LM =
RANKX(
FILTER('table', 'table'[Category] = SELECTEDVALUE('table'[Category])),
SUM('table'[Sales]),
,
DESC
)

Let me know if this works

Thanks