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 Wa...
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

     



     

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