TOP N by few columns

[Ania26]

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
Poland	2019	CocaCola	1	1
Poland	2020	CocaCola	3	1
Poland	2021	Omo	4	1
Poland	2021	Omo	4	1
Poland	2021	CocaCola	3	2

 How to do it?

[Ania26]

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?

Market	Year	Company	Color	Sales
France	2019	Omo	White	1
France	2020	CocaCola	Brown	2
France	2019	CocaCola	Brown	3
Poland	2019	CocaCola	Light Brown	1
Poland	2020	CocaCola	Brown	3
Poland	2021	Omo	Pink	4
Poland	2021	Omo	White	4
Poland	2021	CocaCola	Brown	3

[quantumudit]

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

[wini_R]

Hej @Ania26,

It's not that clear what should be the exact logic here but my best guess would be using RANK function. In partitionby parameter you would need to specify all columns that should be considered for creating ranking:

RANK(

    DENSE,

    ALL(tbl),

    ORDERBY(CALCULATE(SUM(tbl[Sales])), DESC),

    PARTITIONBY(tbl[Year], tbl[Company]) --specify all columns needed

)

[Ania26]

[wini_R]

The error is because you are trying to add a column instead of a measure.
If you need a column, you might want to try something like:

RANK(
    DENSE,
    ALL( tbl3[Year], tbl3[Sales], tbl3[Market] ),
    ORDERBY(tbl3[Year], ASC, tbl3[Sales], DESC),
    PARTITIONBY(tbl3[Year], tbl3[Market])
)

[quantumudit]

Hello @Ania26 

Could you please provide more details about your query? I have a few questions to better understand your requirements:

- What sorting order do you want for the data before ranking?
- Should the data be sorted by Year first, then by Market, and then by Company for the rank calculation?
- Can you clarify what the "Sales" and "Rank" columns represent in your dataset?
- Do you need the results in Power Query or DAX? If DAX, would you prefer a calculated column or a measure?

Your answers will help me better understand the problem and assist you effectively.

Best regards,
Udit

[Ania26]

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 · Accepted solution]

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