Forum Discussion

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

TOP N by few columns

Hello, I would like to have Rank by Year, Market and Compnay : Market Year Company Sales RANK France 2019 Omo 1 1 France 2020 CocaCola 2 1 France 2019 CocaCola 3 2 Pol...
Need Help
  • quantumudit's avatar
    quantumudit
    1 year ago

    Hello Ania26 

    You can use the following DAX formula to create a calculated column in the table

    Rank - Calculated =
VAR _currentMarket = 'Table'[Market]
VAR _currentYear = 'Table'[Year]
RETURN
    RANKX (
        FILTER (
            'Table',
            'Table'[Market] = _currentMarket
                && 'Table'[Year] = _currentYear
        ),
        'Table'[Sales],
        ,
        DESC,
        DENSE
    )

     

    Here is the screenshot of the results table for your reference:

     

     

    Assuming that the "Rank" column provided in the data table the expected ranking, it is evident that for the "France" market in the year "2019", "Coca-Cola" has higher sales than "Omo". Therefore, Omo should be ranked 2 instead 1, as the ranking should be in descending order of sales. This appears to be a human error, but please confirm if otherwise.

     

    I am also attaching the Power BI file for your refernce.

     

    Best Regards,
    Udit

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

     

    Proud to be a Super User

     

     

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

Hello, thank you. This works. 

 

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
quantumudit's avatar
quantumudit
Icon for Super User rankSuper User
1 year ago

Hello Ania26 

The ranking is implemented as a calculated column, and you can view it in the table. When you mention wanting to display the TOPN results with highlighted colors, it suggests that you intend to present this in the report.

 

This appears to be an additional requirement beyond your original query. I’d appreciate it if you could accept the response that addressed your initial question and consider creating a separate ticket for this new request while tagging me. I’d be more than happy to assist you with it. 🙂

 

Additionally, please provide more details about your requirement-such as the columns you want to display, the specific visual you are using, and the colors you’d like to apply. A clearer description will help us understand your needs better.

 

It would also be extremely helpful if you could share a screenshot of your expected outcome. This would allow us to compare results easily and save time for everyone involved.

 

Thanks,
Udit