Top N and Others

[aarontanek]

Hi Everyone,

I have 2 questions on Power BI and hope that someone is able to advise.

#1

The below table refers.

Fruit	Quantity
Apple	30
Kiwi	50
Orange	40
Watermelon	5
Pineapple	8
Total	133

I would like to sort the data in TOP 3; and the remaining park it as ‘Others’; something similar to the below.  Is this achievable using DAX?

Fruit	Quantity
Kiwi	50
Orange	40
Apple	30
Others	13
Total	133

#2

Can we dynamically change the column name in a table visual?

E.g. Quantity (2023) to auto default to current year: Quantity (2024)

Fruit	Quantity (2023)
Apple	30
Kiwi	50
Orange	40
Watermelon	5
Pineapple	8
Total	133

Thanks in advance!

[SachinNandanwar · Accepted solution]

For your first requierement just create a calculated column

FruitName = IF(RANKX(ALLSELECTED('Fruit'),('Fruit'[Quantity]),,DESC,Dense)>3,"Others",Fruit[Fruit])

and use it in the table visual instead of the your original column from the table source .

I am not sure if its possible to achieve your second requieremnt but I am hope I am wrong.The best that is possible based on my knowledge is create a measure 

Title = "Quantity" &  Year(TODAY()) & ""

and add it under  the title property of your table visual

[bhanu_gautam]

@aarontanek , For first one Create a new table by going to modelling 

Top3AndOthers =
VAR Top3 =
TOPN(3, 'YourTable', 'YourTable'[Quantity], DESC)
VAR Others =
EXCEPT(ALL('YourTable'), Top3)
VAR OthersTotal =
SUMX(Others, 'YourTable'[Quantity])
RETURN
UNION(
SELECTCOLUMNS(Top3, "Fruit", 'YourTable'[Fruit], "Quantity", 'YourTable'[Quantity]),
ROW("Fruit", "Others", "Quantity", OthersTotal)
)

And create a measure for Total Quantity

   TotalQuantity = SUM('YourTable'[Quantity])

For second one you can create a measure

DynamicColumnName = "Quantity (" & YEAR(TODAY()) & ")"