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 Tableau Waffle Chart Fast and Easy

In this Tableau tutorial I am serving up some delectable waffles in the form of a fast and easy waffle chart. Watch the video to learn the easiest and quickest way to create a waffle chart in Tableau.

If you’re familiar with the Southeast United States then you know that we love The Waffle House down here. As an homage, I made a simple dashboard in the iconic Waffle House signage style.

A waffle chart depends upon a data connection to the data you wish to visualize and a data connection to the waffle chart template. Once you have these two items setup, you simply create a calculated field that marks the fill percentage in your waffle.

Help yourself to some waffles below:

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.

Create an Interactive Stacked Bar Chart in Tableau

In this post we’ll use the new parameter action functionality in Tableau to create an interactive stacked bar chart. When we select a dimension from the viz, we can drill down into a sub-dimension. Parameter actions are new to Tableau as of version 2019.2. In the video we use an NBA data set which you can download in a workbook if you visit my Tableau Public viz.

This neat little trick relies upon the creation of a parameter named [Team Name Parameter] and two dimensions.

  • Dimension #1 is the [Team Name]
  • Dimension #2 is the [Player Name]

There is an implied hierarchical relationship between [Team Name] and [Player Name] as teams are naturally comprised of players.

We will also create a calculated field named [Player Drill] that is defined as such:

IF [Team Name Parameter] = [Team Name] THEN [Player Name]
ELSE [Team Name]
END

When we place the [Player Drill] calculated field on color on the marks card and setup our parameter actions (watch the video for instruction), the user selection of a [Team Name] on the bar chart will feed the [Team Name Parameter]. This causes all of the players on the selected team to be displayed with their respective breakdown of points scored.

Bonus: There is a FIXED LOD lesson in this video as well as a Star Wars reference which I’m sure you’ll have no issue finding. Here’s a hint:

It’s really not complicated so don’t let all the words here confuse you. Just watch the video and do some great things with your data! Interact with the viz below:

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.

This video was definitely inspired by Kevin Flerlage who has a great blog post on the uses of parameter actions in Tableau. Check it out here.

Create a Customizable Heat Map in Power BI

In this video we’ll learn how to create a customizable heat map in Power BI without using the prepackaged downloadable visual. A heat map (or heatmap) is a graphical representation of data where the individual values contained in a matrix are represented as colors. A heat map helps draw your eye to the most and least popular areas within the matrix. The cells contained within the table either contain color-coded categorical data or numerical data, that is based on a color scale.

Matrix

Wrong Matrix

I have some fun in the video with a dashboard that I constructed using a publicly available data set from Microsoft, but in the lesson we’ll create the following:

Heatmap

Make sure to watch the video, download the data set and follow along with the instructions.

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.

Tableau Sales Dashboard Tutorial using Table Calculations

In this Tableau data visualization tutorial, we’ll learn to use the LOOKUP table calculation function to return sales revenue for the same day last year. A number of different techniques are used in the creation of this dashboard.

I designed this dashboard solely as a teaching exercise to help you understand the LOOKUP function and how to show the same date last year in a separate column.

  • As we learned in a previous video Tableau Table Calculations Simplified, (make sure to watch this video afterwards for more clarity), we’ll compute using specific dimensions and then use “At the level” to make sure our LOOKUP table calculation is performing correctly.
  • The “Show Missing Values” option is selected to fill in date gaps in the data set that do not exist. Ensuring 365 dates per year are present in the visualization enables the offset (i.e., -1) in the LOOKUP calculation to arrive at correct sales revenue from the same day in the previous year.
  • You’ll learn that we can filter on a table calculation by using another table calculation. Filters based on table calculations do not filter out underlying data. Instead, the data is hidden from the view, allowing dimension members to be hidden from the view without impacting the data in the view.

Tableau Order of Operations

Observe the Tableau filter order of operations above. Applying a dimension filter before the Table Calculation filter removes underlying data which affects the proper functioning of Table calculations. Typically, Table Calculations only work on values that are visible in the view. By applying a table calculation (which is last in the order of operations) you preserve underlying data but filter out data from the view.

Interact with this dashboard via the picture link:

You need to read these posts and watch these videos for additional information:

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.

Tableau Sales Dashboard Tutorial: Year Over Year Comparison

In this Tableau data visualization tutorial I used a technique shared by Tableau Zen Master Ryan Sleeper to “equalize” dates across the same axis. This date equalization calculated field enables year over year, quarter over quarter, month over month, week over week and same day last year comparisons.

MAKEDATE(2018,MONTH([Your Date]), DAY([Your Date]))

Equalizer 2

Call in The Equalizer for this Analysis

It’s a pretty clever way of preserving the same month and day of date values across many years and updating their respective years to one common year.

For example, all dates would retain their current month and day but would share the year value of ‘2018’. In this manner, data points from various years can be stacked on top of each other for comparison purposes.


Additionally, by creating a parameter value for a specific date part (i.e., year, month, week, etc.,) the user has control over the level of comparisons in the visualization.

You’ll have to watch the video to see the details. Again, thanks to Ryan Sleeper for sharing this tip with the Tableau community which enabled me to apply it to my dataset and share it with you in video form.

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.

Tableau Table Calculations Made Simple

In this Tableau tutorial I will discuss a basic quick table calculation and try to demystify what is happening behind the scenes. All of the following will hopefully be made more clear in the video but I’m sharing the text below for reference after you watch the video.

I can’t take credit for the content in this post as the Tableau online help site has some quality information on table calculations which I will reference below. There are two important concepts in understanding table calculations: partitioning and addressing.

Key Concepts with Table Calculations: Addressing vs. Partitioning

The dimensions that define how to group the calculation (the scope of data it is performed on) are called partitioning fields. The table calculation is performed separately within each partition.

Partitioning fields break the view up into multiple sub-views (or sub-tables), and then the table calculation is applied to the marks within each such partition.

The remaining dimensions, upon which the table calculation is performed, are called addressing fields, and determine the direction of the calculation.

The direction in which the calculation moves (for example, in calculating a running sum, or computing the difference between values) is determined by the addressing fields.

So when you order the fields in the Specific Dimensions section of the Table Calculation dialog box from top to bottom, you are specifying the direction in which the calculation moves through the various marks in the partition.

When you add a table calculation using the Compute Using options, Tableau identifies some dimensions as addressing and others as partitioning automatically, as a result of your selections.

But when you use Specific Dimensions, then it’s up to you to determine which dimensions are for addressing and which for partitioning.

At the level (Partitioning)

The At the level option is only available when you select Specific Dimensions in the Table Calculations dialog box, and when more than one dimension is selected in the field immediately below the Compute Using options —that is, when more than one dimension is defined as an addressing field.

This option is not available when you’re defining a table calculation with Compute Using, because those values establish partitions by position. But with Specific Dimensions, because the visual structure and the table calculation are not necessarily aligned, the At the level option is available to let you fine-tune your calculation.

Use this setting to set a break (that is, restart of the calculation) in the view, based on a particular dimension. How is this different from just using that dimension for partitioning? In fact, it is partitioning, but it’s partitioning by position rather than by value, which is how partitioning is defined with the Compute Using options.

Filtering on Table Calculations in Tableau

Filtering on Table Calculations in Tableau can be a bit hacky. More often than not, table calculations are dependent upon the data in the view. That means in order to filter on a table calculation, we need a way to preserve underlying data and only hide data from the view.

Filters based on table calculations do not filter out underlying data in the data set, because table calculation filters are applied last in the order of operations. Instead, the data is hidden from the view, allowing dimension members to be hidden from the view without impacting the data in the view.

Notice in the order of operations diagram below how Dimension and Measure filters are applied before Table calculation filters. When trying to filter on a table calculation (which is dependent upon the data in the view) the results may be unexpected. If we turn our dimension or measure into a table calculation, we can then filter the Table calculation at the appropriate level, which preserves underlying data and only hides the table calculation values we wish to filter from the view.

Tableau Order of Operations

The content in this post was quoted from the sources below.

As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.

References:

All views and opinions are solely my own and do NOT necessarily reflect those my employer.