Display top 3 filter - seeking advice.

[steambucky]

Hello

There are many examples of how to display a TOP 3 of your values of data, but my data is just text, not numbers.

For example if my data was looking at the top 3 animal types that appear in our data,

If the data is:

Animal Type

Dog

Dog

Cat

Cat

Mouse

Mouse

Snake

The top 3 would only display Dog, Cats and Mice.

I have looked here at RANKX Function (DAX)  : https://msdn.microsoft.com/en-us/query-bi/dax/rankx-function-dax

You would use this function and then filter the results yes?

Think is i have to add up my animal types to give the data any newmerical value and I am confused how to do this. This was the the example they had.

=RANKX(ALL(Products), SUMX(RELATEDTABLE(InternetSales), [SalesAmount]))   Not sure where to go from here...

any help welcome.

[Phil_Seamark · Accepted solution]

If so, you can do this just using the filter options.  I have attached a PBIX file

[steambucky]

Thanks everyone for the support - a non-coding solution works best for me -  I am NOT a super user...(yet)

CHEERS!

[Thejeswar]

Hi @steambucky,

Based on the example that you have given I have derived my below suggestion. Hope this suits your need!!

As you need to display only the TOP 3 Animals, there is no doubt that you need some column based on which you will define the TOP 3. For this purpose I am considering a pet shop data and that I have an additional no column for each of the animals. In my case it is the "no. sold"column of the below example

Dimension

Data Table

I join these two table based on ID.

Now when I drag the columns Name and Nos. Sold to a table, I will get the sold quantity split by animal

Here is where my Rank Measure comes into picture. It is as follows

Rank Animals = RANKX(ALL(Animal),SUMX(RELATEDTABLE('Animal Sales'), 'Animal Sales'[Nos]),,DESC,Dense)

The Above measure gives me the Rank of animals by Quantity Sold and my table will look like the one below

Animal Table showing Ranks as the last column

Now to show only the TOP 3 Animals, add the measure as a filter to the table and remove the other unwanted columns as shown below

Top 3 Filtered

Best Regards

[Phil_Seamark]

So do you want to display the top three (Dog, Cat& Mouse), ranked by the count of times they occur in your dataset?