Forum Discussion

Ania26's avatar
Ania26
Icon for Helper IV rankHelper IV
1 year ago
Solved

TopN plus OTHER in matrix table

 

Hello, I would like to have TOP 3 fruit and the rest in OTHER and show this in matrix table in the way that in rows we have all "LETTER" shown and in columns only TOP 3 fruits and OTHERS. How to do it?

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Ania26 ,

    I'm sorry for not helping you sort by YEAR column due to an oversight on my part, I've updated my MEASUREMENT and I hope it helps, you can check the matrix below to see if it's the effect you're looking for.

    Top 3 or Other(measure) = 
VAR A=RANKX(FILTER(ALL('Table'),'Table'[YEAR]<=MAX('Table'[YEAR])),CALCULATE(SUM('Table'[VALUE])),,DESC,Dense)
RETURN
IF(
    A<=3,
    MAX('Table'[FRUIT]),
    "Other")

    I hope my answer can solve your problem, if you have further questions, feel free to contact me, I will reply you as soon as I receive the message!

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     



6 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Icon for Solution Sage rankSolution Sage
    1 year ago

    hi Ania26 

     

    You can write a column as follows:

    Rank_Fruit = var rnk=calculate(count(fruit) , filter (your_table , year= earlier (year) && value >= earlier (value)))

    return

    if (rnk >3 , "Others" ,CONVERT(rnk , STRING))

    )

     

    If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi ALL,
    Fisrtly  amitchandak  and Selva-Salimi  thank you for yours solution!
    And Ania26 ,We can also use the TOPN function to fulfill your needs, I hope my idea solves your problem.

    Top 3 Fruits Table = 
UNION(
    TOPN(3, 
        VALUES('Table'[FRUIT]), 
        CALCULATE(SUM('Table'[VALUE])), 
        DESC
    ),
    ROW("FRUIT", "OTHER")
)

    Top 3 or Other(measure) = 
IF(
    MAX('Table'[FRUIT])IN VALUES('Top 3 Fruits Table'[FRUIT]),
    MAX('Table'[FRUIT]),
    "OTHER"
)

    I hope to be able to solve your problem, if you have further questions, you can feel free to contact me, I will be the first time to reply to you after receiving the message, look forward to your reply!

    Hope it helps!

     

    Best regards,
    Community Support Team_ Tom Shen

     

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Ania26's avatar
      Ania26
      Icon for Helper IV rankHelper IV
      1 year ago

      Hello, thank you for your support. Is there a way to group them by year? And also show in the matrix table so display columan and rows?

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Ania26 ,

        I'm sorry for not helping you sort by YEAR column due to an oversight on my part, I've updated my MEASUREMENT and I hope it helps, you can check the matrix below to see if it's the effect you're looking for.

        Top 3 or Other(measure) = 
VAR A=RANKX(FILTER(ALL('Table'),'Table'[YEAR]<=MAX('Table'[YEAR])),CALCULATE(SUM('Table'[VALUE])),,DESC,Dense)
RETURN
IF(
    A<=3,
    MAX('Table'[FRUIT]),
    "Other")

        I hope my answer can solve your problem, if you have further questions, feel free to contact me, I will reply you as soon as I receive the message!

        Hope it helps!

         

        Best regards,
        Community Support Team_ Tom Shen

         

        If this post helps then please consider Accept it as the solution to help the other members find it more quickly.