Forum Discussion

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

TOP N with TOP N

Do you maybe know how in each year, for each Market show top N Companies and for those companies show top n Color?

MarketYearCompanyColorSales
France2019OmoWhite1
France2020CocaColaBrown2
France2019CocaColaBrown3
Poland2019CocaColaLight Brown1
Poland2020CocaColaBrown3
Poland2021OmoPink4
Poland2021OmoWhite4
Poland2021CocaColaBrown3

I would like to display it in the stacked column chart. Show TOP N Companies and the rest in "OTHERS" column and for each company top N Color and the rest in "Others" column. 

quantumudit 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Ania26 

     

    Try this:

     

    Create calculated columns.

     

    Company Group = 
VAR N = 1 // Custom top N ranking.
VAR _rank = 
RANKX(
    FILTER(
        ALL('Table'),
            'Table'[Market] = EARLIER('Table'[Market]) &&
            'Table'[Year] = EARLIER('Table'[Year]) 
    ),
    'Table'[Sales],
    ,
    DESC,
    Dense
)
RETURN
IF(
    _rank <= N, 
    'Table'[Company], 
    "Others"
)

     

    Color Group = 
VAR N = 1 // Custom top N ranking.
VAR _rank = 
RANKX(
    FILTER(
        ALL('Table'),
            'Table'[Market] = EARLIER('Table'[Market]) &&
            'Table'[Company] = EARLIER('Table'[Company]) &&
            'Table'[Year] = EARLIER('Table'[Year]) 
    ),
    'Table'[Sales],
    ,
    DESC,
    Dense
)
RETURN
IF(
    _rank <= N, 
    'Table'[Color], 
    "Others"
)

     

    Create a stacked column chart.

     

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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

     

     

4 Replies

  • Rupak_bi's avatar
    Rupak_bi
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Based on the sample data, please illustrate the look of the desired output for better undershanding. 

  • Akash_Varuna's avatar
    Akash_Varuna
    Icon for Super User rankSuper User
    1 year ago

    Hi Ania26  Could You try this please

    1. Rank Companies:
      CompanyRank = RANKX(ALL('Table'[Company]), SUM('Table'[Sales]), , DESC)

    2. Group Companies:
      CompanyCategory = IF([CompanyRank] <= N, 'Table'[Company], "Others")

    3. Rank Colors:
      ColorRank = RANKX(ALL('Table'[Color]), SUM('Table'[Sales]), , DESC)

    4. Group Colors:
      ColorCategory = IF([ColorRank] <= N, 'Table'[Color], "Others")

    5. Create Chart:

      • Axis: Year, Market.
      • Values: SUM(SALES).

    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance

  • quantumudit's avatar
    quantumudit
    Icon for Super User rankSuper User
    1 year ago

    Hello Ania26 

    Could you please specify the number for "N" when you to Top-N results? Additionally, will the top companies be determined based on the ranking we have conducted?

    Thanks,

    Udit

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Ania26 

     

    Try this:

     

    Create calculated columns.

     

    Company Group = 
VAR N = 1 // Custom top N ranking.
VAR _rank = 
RANKX(
    FILTER(
        ALL('Table'),
            'Table'[Market] = EARLIER('Table'[Market]) &&
            'Table'[Year] = EARLIER('Table'[Year]) 
    ),
    'Table'[Sales],
    ,
    DESC,
    Dense
)
RETURN
IF(
    _rank <= N, 
    'Table'[Company], 
    "Others"
)

     

    Color Group = 
VAR N = 1 // Custom top N ranking.
VAR _rank = 
RANKX(
    FILTER(
        ALL('Table'),
            'Table'[Market] = EARLIER('Table'[Market]) &&
            'Table'[Company] = EARLIER('Table'[Company]) &&
            'Table'[Year] = EARLIER('Table'[Year]) 
    ),
    'Table'[Sales],
    ,
    DESC,
    Dense
)
RETURN
IF(
    _rank <= N, 
    'Table'[Color], 
    "Others"
)

     

    Create a stacked column chart.

     

     

    Here is the result.

     

     

    Regards,

    Nono Chen

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