cancel
Showing results for
Did you mean:
Resolver III

## top 4 categories based on a measure from another table

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.

2 REPLIES 2
Super User

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
)``````

Resolver III

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

Thanks,

Anthony Joseph

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors