I want to display the name of the top 5 sold items in a shop by putting each name of the item in a card (I am aware making a table is much easier but would rather get the items individually for design purposes).
So im writing a DAX measure for each top x item using the below code. The problem is that when the count for multiple items is the same, they have the same rank, so lets say rank 1 and rank 2 are the same count, rank 1 would display the item name but rank 2 would display blank. adding dense to RANKX skips the 2nd ranked item and goes directly to the 3rd which is not what i want.
I did have some sort of solution but could not implement it which was doing a seperate table with the items grouped by the count. Then i would add an index column which numbers the items in order of descending count and updates everytime the data is refreshed. If this is possible to implement if the above problem is not fixed, then that would be great.
2ndHighestItem =
VAR ItemCounts =
SUMMARIZE('My table', 'My table'[Items], "ItemCount", COUNTROWS('My table'))
VAR RankedItems =
ADDCOLUMNS(
ItemCounts,
"Rank", RANKX(ItemCounts, [ItemCount], , DESC)
)
RETURN
CALCULATE(
MAX('My table'[Items]),
FILTER(RankedItems, [Rank] = 2)
)
