top 4 categories based on a measure from another table

[AnthonyJoseph]

Hello Community,

I have been struggling with a calculation for past few days. Hoping someone could help here.

I have two tables (table 1 and table 2) and I want to find the top 4 categories for the latest/maximum year selected from year (multi-select) slicer from table 1 along with its value for the yers selected.

For example: if I select years 2022,2021 and 2020 the visual should display the top 4 categories for 2022 (based on a measure) and the categories corresponding value for 2021and 2020 as well.

The visual should not show any category that is not in top 4 for the year 2022. 

Measure:

Measure = 
var s = sum(Table1[Local movement]) 
return 
if(s <> 0, sum(Table1[Charge]) / s, s)

Table 1:

Temp ID	Country	Year	Charge	Local movement
1	India	2020	100	0
2	United Kingdom	2020	200	20
3	United States	2022	130	0
6	India	2021	11	40
7	United Kingdom	2021	198	0
8	United States	2022	340	0
10	India	2022	151	87
11	United kingdom	2020	146	0
12	United States	2021	142	13

Table 2:

Temp ID	Category	TR
1	Bike	10
1	Car	20
1	Scooter	30
1	Bicycle	19
1	Bicycle	21
6	Bike	50
6	Car	30
6	Scooter	20
6	Auto	30
6	Auto	31
10	Bike	10
10	Car	30
10	Scooter	22
10	Bicycle	22
10	Auto	11
2	Bike	10
2	Car	20
2	Scooter	30
2	Bicycle	19
2	Bicycle	21
7	Bike	31
7	Car	87
7	Scooter	81
7	Auto	47
3	Auto	34
3	Bike	73
3	Car	74
3	Scooter	67
12	Bike	10
12	Car	100
12	Scooter	25
12	Bicycle	29
12	Bike	61
12	Car	68

Any help on this is really appreciated.

[tamerj1]

Hi @AnthonyJoseph 
Here is a sample file file with the solution https://we.tl/t-kKIBIDqOE5
Place this measure in the filter pane of the visual

Filter = 
VAR LastYearVisible = CALCULATE ( MAX ( Table1[Year] ), ALLSELECTED () )
RETURN
    RANKX ( 
        CROSSJOIN ( ALL ( Table1[Year] ), ALL ( Table2[Category] ) ),
        CALCULATE ( [Measure], Table1[Year] = LastYearVisible ),
        ,, Dense
    )

[AnthonyJoseph]

Thanks @tamerj1 . Is there any way we can sort the column in  graph in descending order of the rank?

Thanks,

Anthony Joseph