topic Re: Top N and Others in DAX Commands and Tips

Top N and Others

aarontanek — Thu, 01 Aug 2024 08:46:38 GMT

Hi Everyone,

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

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

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!

Re: Top N and Others

bhanu_gautam — Thu, 01 Aug 2024 09:05:48 GMT

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()) & ")"

Re: Top N and Others

SachinNandanwar — Thu, 01 Aug 2024 10:34:07 GMT

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