Forum Discussion

Ania26's avatar
Ania26
Helper 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
Helper IV
1 year ago

Hello,

 

1) Sorting by Sales Desc

2) For each Market , in each year I want to see Company with Rank by Sales

3) Sales in just sales in Euro and Rank is ranking

4) I want to have a column because then I want to use that Rank as slicer

quantumudit's avatar
quantumudit
Super User
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