Forum Discussion

aarontanek's avatar
aarontanek
Regular Visitor
2 years ago
Solved

Top N and Others

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!

Top N
  • SachinNandanwar's avatar
    SachinNandanwar
    2 years ago

    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

     



     

2 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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()) & ")"
  • SachinNandanwar's avatar
    SachinNandanwar
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    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