In this video we’ll venture outside of the default Power BI TOPN functionality used to isolate the top and bottom N values in a visual. Because you’re an astute follower of my blog and YouTube channel, you want to know more than default functionality. The key to pulling off this feat lies with the RANKX function. By using RANKX to provide a ranking to each row in our data set, we can then determine the TOP and BOTTOM 10 values.
Of course watch the video for further breakdown, but they key to using RANKX effectively in Power BI is to use the ALL function as a parameter. In this way the contents of the entire table are considered for appropriate ranking.
The DESC or ASC parameters will enable the calculation of a rank in either descending or ascending sort order.
And finally the DENSE parameter tells RANKX how ties should be handled in the data. For example if you provide DENSE as a parameter, if 10 values are tied with a rank of 1 then the next value will receive a rank of 2.
Here is an example use of RANKX that will return a ranking of Sales Price by Product, that ranks the results in descending order (highest Sales receive the lowest ranks) and ties are in contiguous order. Watch the video to determine how to calculate the BOTTOM 10 ranking and to use RANKX to enable the top or bottom 10 values in a visualization.
ProductRank = RANKX(ALL('Company Sales Data'[Product]), calculate(sum('Company Sales Data'[Sales Price])),,DESC,dense)
As a refresher, check out this popular video to build the calendar table referenced in this video: Power BI Dashboard Tutorial: Year over Year Difference Analysis
If you find this type of instruction valuable make sure to subscribe to my Youtube channel.
All views and opinions are solely my own and do NOT necessarily reflect those my employer.