Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Lina2023
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

Hi @Lina2023 

 

Here's some dummy data

 

"Table"

 

vnuocmsft_0-1714032780161.png

 

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.

 

vnuocmsft_1-1714032874498.png

Regards,

Nono Chen

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

View solution in original post

6 REPLIES 6
Lina2023
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! 

Lina2023_1-1713862876872.png

 

 
 

Hi @Lina2023 

 

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.

 

vnuocmsft_0-1713920922344.png

Regards,

Nono Chen

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

 

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

Lina2023_0-1713950726805.png

 

Hi @Lina2023 

 

Here's some dummy data

 

"Table"

 

vnuocmsft_0-1714032780161.png

 

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.

 

vnuocmsft_1-1714032874498.png

Regards,

Nono Chen

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

v-nuoc-msft
Community Support
Community Support

Hi @Lina2023 

 

For your question, here is the method I provided:

 

Here's some dummy data

 

"Table"

vnuocmsft_0-1713842463592.png

 

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.

 

vnuocmsft_1-1713842618897.png

 

Regards,

Nono Chen

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

 

 

Kaviraj11
Responsive Resident
Responsive Resident

Try this 

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

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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