Top and Bottom 10 Products by Sales Using RANKX in Power BI

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.

Power BI Top 10 and Bottom 10 Thumbnail

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.


Create a Bar Chart in Python using Matplotlib and Pandas

In this Python visualization tutorial you’ll learn how to create and save as a file a stylish bar chart in Python using Matplotlib and Pandas. We’ll easily read in a .csv file to a Pandas dataframe and then let Matplotlib perform the visualization. As a bonus you’ll also learn how to save the plot as a file.

I am using the Anaconda Distribution which is a great one stop shop for all your python needs as it is a free and open source distribution of python. I love this option because it gives you the ability to quickly download multiple python packages for analyses and visualizations. As a bonus it includes Jupyter notebooks (which is a web based environment for writing code).

Packages Used

In case you aren’t familiar with Python packages:

  • Pandas is the killer app so to speak for reading, writing and wrangling data.
  • Matplotlib is the visualization plotting library we will use.
  • Pyplot is collection of functions that enables changes to a figure. Think of a figure as a container that can contain multiple plots know as axes. We can plot our bar chart in the figure plotting area and then enhance the plot with labels, etc.
  • Finally OS is a module that enables the use of operating system functionality. We’ll use this package to point our working directory to where our .csv file is located.

Once you follow the instructions in the video, you’ll produce an image like the following which can be saved to a directory location of your choice.

Keep this Matplotlib style sheet reference handy for changing up your visual.

NBA Blocks

As always, 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.