Tableau Vs Power BI – Rank within Partition
Due to its many uses, rank (or index in Tableau) is one of the most frequently used functions in both Tableau and Power BI. Once you are familiar with the fundamentals of either tool, you might come across the Rank() function. This video blog will demonstrate how to use Tableau and Power BI to rank data by category or division.
To follow along with me, you can grab the data source from this page. Additionally, you are welcome to download the full solution workbooks from this page. Please be aware that my blogs are video-oriented, so watch the video attentively and make sure the written or typed information complements it.
Here is a snapshot of the data source showing that each category has multiple subcategories, and we are only interested in displaying the top 3 categories in terms of sales.
Below is the expected output for Power BI, Top 3 Sub-Categories under each category
Below is the expected output for Tableau, Top 3 Sub-Categories under each category
Starting with Tableau, please. To follow along with me, please watch the step-by-step solution video on the RHS.
RANK(sum([Sales])) is the function that is being utilized here.
RANK(expression, [‘asc’ | ‘desc’])
Returns the standard competition rank for the current row in the partition. Identical values are assigned an identical rank. Use the optional 'asc' | 'desc' argument to specify ascending or descending order. The default is descending.
For information on different ranking options, see Rank calculation.
The idea of addressing and partitioning is one of the main distinctions between Tableau and Power BI; if you want to learn more about this idea, go on this link.
Now let’s turn to Power BI video for step-by-step analysis.
RANKX serves a similar purpose for us.
RANKX(<table>, <expression>[, <value>[, <order>[, <ties>]]])
table
Any DAX expression that returns a table of data over which the expression is evaluated.
expression
Any DAX expression that returns a single scalar value. The expression is evaluated for each row of table, to generate all possible values for ranking.
value
(Optional) Any DAX expression that returns a single scalar value whose rank is to be found. When the value parameter is omitted, the value of expression at the current row is used instead.
order
(Optional) A value that specifies how to rank value, low to high or high to low:
Skip
The next rank, after a ties, the rank value of the tie plus the count of tied values. For example, if five (5) are tied with rank of 11 then the next value will receive a rank of 16(11+5)
Dense
The next rank value, after a tie, is the next value. For example, if five values are tied with rank of 11 then the next value will receive a rank of 12
I hope you were able to grasp how to rank within a partition as well as how Tableau and Power BI tackled the same issue in various ways from this video blog. As said above, I would advise you to familiarize yourself with the Tableau Addressing and Partitioning concept here, which is the key distinction between Tableau and Power BI in terms of this use case.
For instance, with Tableau, there is a dedicated function for dense ranking (RANK_DENSE), whereas Power BI’s RANKX function can handle this, you can notice the last parameter of RANKX above. Since we wanted to first emphasize the fundamental concept, we have not explored the many versions of RANK in Tableau and Power BI. However, please expect another blog article on the same topic.
#1 in the world to be Tableau Ambassador and Power BI Super User,
