Forum Discussion
Helper IIIDisplay top 3 filter - seeking advice.
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.
If so, you can do this just using the filter options. I have attached a PBIX file
4 Replies
- Phil_Seamark
Microsoft Employee
If so, you can do this just using the filter options. I have attached a PBIX file
steambuckyThanks everyone for the support - a non-coding solution works best for me - I am NOT a super user...(yet)
CHEERS!
- 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?
- Thejeswar
Super User
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
