Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Have a table with sales for specific restaurants - would like to rank top 50 by 'xMonthActual_TABC' sales and 'xYearActual_TABC' sales. (see screenshot below)
Appreciate all insight & recommendations! ~db
Solved! Go to Solution.
That's correct.
This is because rank was created with two columns, [location_name] and [location_city].
When you select a city, it does the rank considering the two columns. To work as you wish, consider only the [location_name] column in the rank and put the [location_city] column as the filter.
To do this, change the two measures.
Rank MonthPrevius = IF ( HASONEVALUE (ft_SALES [location_name]); RANKX (ALL (ft_SALES [location_name]); [xMonthPreviusToDate_TABC] ;; DESC; DENSE)) Rank MonthActual = IF ( HASONEVALUE (ft_SALES [location_name]); RANKX (ALL (ft_SALES [location_name]); [xMonthActual_TABC] ;; DESC; DENSE))
Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
Hello Guys
I need to create 2 ranking
Can anyone help me with it?
HI, @dentonblake
LET'S CREATE A MEASURE
1. Create the total values you want to rank (in this case they are already created xMonthPreviusToDate_TABC, xMonthActual_TABC). If you apply in other examples, just calculate something like
Total_Sales_Last_Year = SUM (ft_SALES [total_LY])
But let's use the ones you already have.
2. Create the two measures to rank the values in this case
we are ranking by the two columns [location_name] and [location_city] here you can put one only.
Rank MonthPrevius = IF ( HASONEVALUE (ft_SALES [location_name]); RANKX (ALL (ft_SALES [location_name]; ft_SALES [location_city]); [xMonthPreviusToDate_TABC] ;; DESC; Dense)) Rank MonthActual = IF ( HASONEVALUE (ft_SALES [location_name]); RANKX (ALL (ft_SALES [location_name]; ft_SALES [location_city]); [xMonthActual_TABC] ;; DESC; Dense))
3. Create a measure to rank 25 item (50 is a lot, but you can change it)
Top 25 MonthPrevius = IF ([Rank MonthPrevius] <= 25; [xMonthPreviusToDate_TABC]; blank ()) Top 25 Rank MonthActual = IF ([Rank MonthActual] <= 25; [xMonthActual_TABC]; blank ())
4. Create a ranking presentation measure.
Position MonthPrevius = IF ([Rank MonthPrevius] <= 25; [Rank MonthPrevius]; BLANK ()) Position MonthActual = IF ([Rank MonthActual] <= 25; [Rank MonthActual]; BLANK ())
5. Create another table to display the result place the columns
[Location_city], [location_name], [Position MonthPrevius], [Top 25 Rank MonthPrevius], [Position MonthActual], [Top 25 Rank MonthActual]
Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
Rfranca-
Works great when ranking across all cities - but, if I want to rank the Top 25 (or 50) in one city (e.g. Dallas) then it doesn't work (see below).
Thank you for all of your help!
~db
That's correct.
This is because rank was created with two columns, [location_name] and [location_city].
When you select a city, it does the rank considering the two columns. To work as you wish, consider only the [location_name] column in the rank and put the [location_city] column as the filter.
To do this, change the two measures.
Rank MonthPrevius = IF ( HASONEVALUE (ft_SALES [location_name]); RANKX (ALL (ft_SALES [location_name]); [xMonthPreviusToDate_TABC] ;; DESC; DENSE)) Rank MonthActual = IF ( HASONEVALUE (ft_SALES [location_name]); RANKX (ALL (ft_SALES [location_name]); [xMonthActual_TABC] ;; DESC; DENSE))
Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.
Best Regards,
Rfranca
Rfranca, thank you again ... you're my hero! ~db
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
143 | |
77 | |
63 | |
51 | |
47 |
User | Count |
---|---|
212 | |
82 | |
61 | |
60 | |
57 |