Forum Discussion
Rank top 3 by differed categories
hi, I have a table with data:
| Car | Country | Sales | Growth |
| BMW | Germany | 100 | 25 |
| Mercedes | Germany | 200 | 30 |
| VW | Germany | 300 | 20 |
| Audi | Germany | 400 | 10 |
| Toyota | Japan | 100 | 10 |
| Citroen | France | 200 | 15 |
| Aston Martin | Great Britain | 300 | 20 |
| Seat | Spain | 400 | 25 |
| Ford | USA | 500 | 30 |
Table has many more columns, which are not used in visualization.
I want to create a visual (table), where I will show:
- top 3 cars from germany by sales
- top 3 cars from rest of the countries by sales (and is not Toyota)
- Toyota
- top 1 car by growth (is not top 3 from germany, is not top 3 from rest of countries and is not Toyota)
Result should be (ideally in this order):
| Car | Sales |
| Audi | 400 |
| VW | 300 |
| Mercedes | 200 |
| Ford | 500 |
| Seat | 400 |
| Aston Martin | 300 |
| Toyota | 100 |
| BMW | 100 |
I tried with the code below (without top 1 car by growth), but it is not working:
var _germany = TOPN (
3,
FILTER (
ALLSELECTED ( Table[Car] ),
SELECTEDVALUE ( Table[Car] ) <> "Toyota" && SELECTEDVALUE ( Table[Country] ) = "Germany"
),
[Sales]
)
var _nongermany = TOPN (
3,
FILTER (
ALLSELECTED ( Table[Car] ),
SELECTEDVALUE ( Table[Car] ) <> "Toyota" && SELECTEDVALUE ( Table[Country] ) <> "Germany"
),
[Sales]
)
var _toyota = FILTER (
ALL ( Table[Car] ),
SELECTEDVALUE ( Table[RetailBrandCode] ) = "Toyota"
)
return
CALCULATE (
[Growth Drivers - Custom Group Current Whole Number],
UNION (
_germany,
_nongermany
_toyota
)
,VALUES ( Table[Car] )
)
Hi lokosrio ,
Based on the information you have provided, you can follow the steps below to solve your problem:
1. Add a column to sort the sales.Rank_Sales = IF ( 'Table'[Car] = "Toyota", BLANK (), IF ( 'Table'[Country] = "Germany", RANKX ( FILTER ( 'Table', 'Table'[Country] = EARLIER ( 'Table'[Country] ) ), 'Table'[Sales] ), RANKX ( FILTER ( 'Table', 'Table'[Country] <> "Germany" ), 'Table'[Sales] ) ) )2.Add a flag column, filter results with flag=1.
Flag = VAR _rank_growth = IF ( 'Table'[Rank_Sales] > 3 && 'Table'[Car] <> "Toyota", RANKX ( FILTER ( 'Table', 'Table'[Rank_Sales] > 3 && 'Table'[Car] <> "Toyota" ), 'Table'[Growth] ), BLANK () ) RETURN IF ( 'Table'[Rank_Sales] <= 3 || _rank_growth = 1 || 'Table'[Car] = "Toyota", 1, 0 )3.Add result column.
Rank_Result = VAR _1 = IF ( 'Table'[Rank_Sales] <= 3, 'Table'[Rank_Sales], 0 ) VAR _2 = IF ( 'Table'[Country] = "Germany", _1, _1 + 3 ) VAR _3 = IF ( 'Table'[Country] <> "Germany" && _2 = 3, 7, _2 ) RETURN IF ( _3 = 0, 8, _3 )Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi lokosrio ,
Based on the information you have provided, you can follow the steps below to solve your problem:
1. Add a column to sort the sales.Rank_Sales = IF ( 'Table'[Car] = "Toyota", BLANK (), IF ( 'Table'[Country] = "Germany", RANKX ( FILTER ( 'Table', 'Table'[Country] = EARLIER ( 'Table'[Country] ) ), 'Table'[Sales] ), RANKX ( FILTER ( 'Table', 'Table'[Country] <> "Germany" ), 'Table'[Sales] ) ) )2.Add a flag column, filter results with flag=1.
Flag = VAR _rank_growth = IF ( 'Table'[Rank_Sales] > 3 && 'Table'[Car] <> "Toyota", RANKX ( FILTER ( 'Table', 'Table'[Rank_Sales] > 3 && 'Table'[Car] <> "Toyota" ), 'Table'[Growth] ), BLANK () ) RETURN IF ( 'Table'[Rank_Sales] <= 3 || _rank_growth = 1 || 'Table'[Car] = "Toyota", 1, 0 )3.Add result column.
Rank_Result = VAR _1 = IF ( 'Table'[Rank_Sales] <= 3, 'Table'[Rank_Sales], 0 ) VAR _2 = IF ( 'Table'[Country] = "Germany", _1, _1 + 3 ) VAR _3 = IF ( 'Table'[Country] <> "Germany" && _2 = 3, 7, _2 ) RETURN IF ( _3 = 0, 8, _3 )Final output:
How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired out put and pbix file without privacy information.
Best Regards,
Ada Wang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.