topic Re: show rank in cards (without any row) in DAX Commands and Tips

show rank in cards (without any row)

logan_logan — Tue, 10 Oct 2023 21:00:38 GMT

Hi I want to show rank in cards.

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)

Re: show rank in cards (without any row)

DallasBaba — Tue, 10 Oct 2023 22:26:58 GMT

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

Re: show rank in cards (without any row)

logan_logan — Wed, 11 Oct 2023 15:32:08 GMT

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?

Re: show rank in cards (without any row)

DallasBaba — Wed, 11 Oct 2023 16:22:28 GMT

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

Re: show rank in cards (without any row)

Anonymous — Thu, 12 Oct 2023 03:32:55 GMT

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.

Re: show rank in cards (without any row)

logan_logan — Thu, 12 Oct 2023 14:05:34 GMT

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