Forum Discussion
Helper IVTOP 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 |
| 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?
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: LinkTreeProud to be a Super User
8 Replies
- quantumudit
Super User
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
Ania26Hello,
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
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: LinkTreeProud to be a Super User
wini_RSolution Supplier
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]) )
- 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