cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Regular Visitor

## Rank number for a particular category in a card visual

Hi, I've done a lot of research but still can't work out how to use a card visual to display a ranking number (say number 8 out of 12) for a category. Say I want to say Apples are ranked number 8 out of the 12 fruit in terms of % profit in sales. So I want the card to just show 8. Would be grateful for any tips with the dax code.

1 ACCEPTED SOLUTION
Community Support

Here's some dummy data

"Table"

Create measures.

``rank = RANKX(ALLEXCEPT('Table', 'Table'[year], 'Table'[color]), CALCULATE(SUM('Table'[% profit])),, DESC, Dense)``

``rank % profit = MAXX(FILTER('Table', 'Table'[category] = "Apple"), 'Table'[rank])``

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
Regular Visitor

Thank you both for your replies, very helpful! What would you suggest I need to do if I wanted to always display the ranking number of just apples, without a slicer, as its' the only fruit I am interested in? Ideally, I'd liket to get it automated to say '5 out of 12' but just a card with '5', i.e. the ranking, will do. Thank you very much in advance!

Community Support

I made some changes as you requested:

Create measures.

``rank = RANKX(ALL('Table'), CALCULATE(SUM('Table'[% profit])),, DESC, Dense)``

``rank % profit = MAXX(FILTER('Table', 'Table'[category] = "Apple"), 'Table'[rank])``

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regular Visitor

Thanks again!

And one more please, if I wanted to add a filter by colour and out of the available list to only choose the 'green' fruit to rank. So, I'd want to know apple's ranking out of the green fruit (without having to create a separate table). Is it possbile to accommodate that, do you think, as well as having a slicer on a year, so what was the ranking of apples out of all green fruit in 2023 or 2024? Thank you

Community Support

Here's some dummy data

"Table"

Create measures.

``rank = RANKX(ALLEXCEPT('Table', 'Table'[year], 'Table'[color]), CALCULATE(SUM('Table'[% profit])),, DESC, Dense)``

``rank % profit = MAXX(FILTER('Table', 'Table'[category] = "Apple"), 'Table'[rank])``

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

For your question, here is the method I provided:

Here's some dummy data

"Table"

Create a measure. When the value in the slicer is selected, the ranking is displayed in the card visual object.

``````rank % profit =
IF(
ISFILTERED('Table'[category]),
RANKX(ALL('Table'), CALCULATE(SUM('Table'[% profit])),, DESC, Dense),
BLANK()
)``````

Here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Responsive Resident

Try this

RANKX(
FILTER(
'TableName',
ColumnName = EARLIER(ColumnName)
),
'Tablename'[Count],,DESC,Dense
)

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors