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.
Top 5 with Other Column
Hi Team, Need DAX help! I am looking for Top5 customer and their top unit in column.Apart from the Top 3 units which are in column I want to add all sales in Other column and then a Total Sales column. I am trying to create dax function but its not working. Below is the sample data set Customer Unit Sales AA SCM 500 AB Procurment 600 AC Sales 300 AD Field 209 AA Marketing 948 AB Procurment 305 AZ Marketing 382 AF Services 633 AK Field 993 AN Procurment 244 AD Field 362 AK Marketing 750 AD Procurment 250 Expected Output would be like Top5 customer in row, Top3 unit in column(having the sales value for that unit) and "other" column (Having sales sum except the top3 value sale) and Grand total(Sum of all sales) at the end. Customer Field Marketing Procurment Other Grand Total AA 948 500 1448 AB 905 905 AD 571 250 250 821 AF 633 633 AK 993 750 1743 Requesting you to do the needful. Regards Uphar