Showing Top N and "Others" in Table Chart

Options

Hi,

I have a table chart which shows "Product" and "Sales" in a table chart. I would like to show Top N Products based on Sales (descending) and group the rest of the Products as "Others".

Is this possible?

Tagged:

Best Answer

  • ArborRose
    ArborRose Coach
    Answer ✓

    Sample data:

    Product,Sales
    Product A,1200
    Product B,950
    Product C,870
    Product D,1500
    Product E,400
    Product F,200
    Product G,1750
    Product H,300
    Product I,100
    Product J,600

    ETL:

    image.png

    Group By:

    image.png

    Rank:

    image.png

    Gives:

    image.png

    Then create beast mode as Product Group:

    CASE WHEN `Top N` <= 5 THEN `Product` ELSE 'Others' END
    
    image.png

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

Answers

  • Create an ETL and use the Rank and Window tile to rank products by sales. Or beast mode with something like

    RANK() OVER (ORDER BY SUM(`Sales`) DESC)

    Once you have a ran defined you can create a beast mode

    CASE 
    WHEN {rank} < = 5 THEN `Product`
    ELSE 'Others'
    END

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • ArborRose
    ArborRose Coach
    Answer ✓

    Sample data:

    Product,Sales
    Product A,1200
    Product B,950
    Product C,870
    Product D,1500
    Product E,400
    Product F,200
    Product G,1750
    Product H,300
    Product I,100
    Product J,600

    ETL:

    image.png

    Group By:

    image.png

    Rank:

    image.png

    Gives:

    image.png

    Then create beast mode as Product Group:

    CASE WHEN `Top N` <= 5 THEN `Product` ELSE 'Others' END
    
    image.png

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **

  • b_rad
    b_rad Contributor

    @ArborRose Thank you for the answer. Really appreciate it. I was hoping to do something within the analyzer itself. Hopefully they add this feature.