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.

Advertisements

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.

Power BI Dashboard Tutorial: Year over Year Difference Analysis

I want you to increase your efficiency and to stop using spreadsheets for every single analysis.

Everybody works with time series data at some point in time. Year over year (also known as YoY) analysis is one of the most useful analyses you can perform to determine changes, analyze growth and recognize trends in quantity on an annual basis.

Unfortunately, most data preparers are used to performing some unaesthetic flavor of this analysis using only Excel (looking at you FP&A). Without the benefit of using visualization to easily recognize trends, data consumers are forced to work harder to tease out the most salient information.

If you have access to Power BI Desktop (available for free), then you can perform a tabular year over year difference calculation and then tie that information to a bar chart that will help you visualize the variances.

In this video I will show you how to create a calendar table in DAX (Microsoft’s formula expression language) and use that table to enable a year over year analysis of customer orders at fictional Stark Industries. You don’t need to be an expert in DAX to take advantage, just type in the date calendar formula you see in the video and tweak the simple calculations to fit your data.

You could obviously perform a simple YoY analysis in Excel, but I want you to stay relevant and learn something new!

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

 

Tableau Dashboard Tutorial: Dot Strip Plot

In this video tutorial I describe a dashboard that I put together that displays the distribution of various NBA player statistics. I use the always handy parameter to enable the user to choose which statistics are displayed on the dashboard. Although I’m showing sports statistics measures in this dashboard, it could easily be repurposed to show the distribution of a variety of business related metrics.

I break the dashboard up into three areas: histogram, dot strip plot, and heat map. In the second part of the video, I describe in detail how to build out a jittered dot strip plot. The benefit of the jittered dot strip plot is that the marks representing NBA players obstruct each other much less as compared to the linear dot strip plot.

Techniques used in the dashboard were previous outlined in my Ultimate Slope Graph and How to Use Jittering in Tableau (Scattered Data Points) posts.

Feel free to head to my Tableau Public page and download the workbook for yourself. Drop me a line in the comments or on YouTube if you learned something.

As always, do great things with your data!

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

Create Rounded Bar Charts in Tableau

Part 1: How to Make Rounded Bar Charts in Tableau

In this post you’re getting two videos for the price of one (considering they’re all free for now, that’s a good thing). I put together a relatively simple dashboard to help illustrate a few intermediate level concepts. In this first video I take a look at the number of total assists by NBA players during the 2017-2018 season. In case you were wondering, Russell Westbrook led the league in assists during that season. If you don’t know who Russell Westbrook is, then skip this Tableau stuff and watch the last video immediately (and then come back to the Tableau stuff).

In the first Tableau dashboard video, you’ll learn two concepts:

  • How to make rounded bar charts;
  • How to filter the number of bar chart marks via use of a parameter;

Part 2: Apply Custom Sorting in Tableau

In the second video I build upon the dashboard built in the first video by showing you how to add a custom sort. The custom sort relies upon the creation of a parameter and a calculated field. The parameter and calculated field enable the user to select either a dimension (e.g., Player Name) or a measure (e.g., sum of assists) from a drop down box and the visualization will sort ascending or descending as requested.

The calculated field relies upon the RANK_UNIQUE function.

In this context, RANK_UNIQUE returns the unique rank of each player’s assist total. The key with RANK_UNIQUE is that identical values are assigned different ranks. As an example, the set of values (6, 9, 9, 14) would be ranked (4, 2, 3, 1), as no tied rankings are allowed.

Part 3: Interact with the Dashboard

Bonus: Russell Westbrook on the Attack

For those of you who do not know who Russell Westbrook is, I’ve got you covered. These aren’t assists but in these situations, he didn’t need to pass!

References:

Thanks to both the Tableau Magic blog for outlining the concept of rounded bar charts and the VizJockey blog for the custom sort methodology. Check out and support these  blogs!

As always, do great things with your data!

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

Tableau Sales Dashboard Performance

The following is a guest post.

Business heads often use KPI tracking dashboards that provide a quick overview of their company’s performance and well-being. A KPI tracking dashboard collects, groups, organizes and visualizes the company’s important metrics either in a horizontal or vertical manner. The dashboard provides a quick overview of business performance and expected growth.

An effective and visually engaging way of presenting the main figures in a dashboard is to build a KPI belt by combining text, visual cues and icons. By using KPI dashboards, organizations can access their success indicators in real time and make better informed decisions that support long-term goals.

What is a KPI?

KPIs (i.e. Key Performance Indicators) are also known as performance metrics, performance ratios or business indicators. A Key Performance Indicator is a measurable value that demonstrates how effectively a company is achieving key business objectives.

A sales tracking dashboard provides a complete visual overview of the company’s sales performance by year, quarter or month. Additional information such as the number of new leads and the value of deals can also be incorporated.

Example of KPIs on a Sales Dashboard:

  • Number of New Customers and Leads
  • Churn Rate (i.e. how many people stop using the product or service)
  • Revenue Growth Rate
  • Comparison to Previous Periods
  • Most Recent Transactions
  • QTD (quarter to date) Sales
  • Profit Rate
  • State Wise Performance
  • Average Revenue for Each Customer

Bringing It All Together with Dashboards and Stories

An essential element of Tableau’s value is delivered via dashboards. Well-designed dashboards are visually engaging and draw in the user to play with the information. Dashboards can facilitate details-on-demand that enable the information consumer to understand what, who, when, where, how and perhaps even why something has changed.

Best Practices to Create a Simple and Effective Dashboard to Observe Sales Performance KPIs

A well-framed KPI dashboard instantly highlights problem areas. The greatest value of a modern business dashboard lies in its ability to provide real-time information about a company’s sales performance. As a result, business leaders, as well as project teams, are able to make informed and goal-oriented decisions, acting on actual data instead of gut feelings. The choice of chart types on a dashboard should highlight KPIs effectively.

Bad Practices Examples in a Sales Dashboard:

  • A sales report displaying 12 months of history for twenty products; 12 × 20 = 240 data points.
    • Multiple data points do not enable the information consumer to effectively discern trends and outliers as easily as a time-series chart comprised of the same information
  • The quality of the data won’t matter if the dashboard takes five minutes to load
  • The dashboard fails to convey important information quickly
  • The pie chart has too many slices, and performing precise comparisons of each product sub-category is difficult
  • The cross-tab at the bottom requires that the user scroll to see all the data

Now, we will focus on the best practices to create an effective dashboard to convey the most important sales information. Tableau is designed to supply the appropriate graphics and chart types by default via the “Show me” option.

I. Choose the Right Chart Types

With respect to sales performance, we can use the following charts to show the avg. sales, profits, losses and other measures.

  • Bar charts to compare numerical data across categories to show sales quantity, sales expense, sales revenue, top products and sales channel etc. This chart represents sales by region.

1

  • Line charts to illustrate sales or revenue trends in data over a period of time:

2

  • A Highlight table allows us to apply conditional formatting (a color scheme in either a continuous or stepped array of colors from highest to lowest) to a view.

3

  • Use Scatter plots or scatter graphs to investigate the relationship between different variables or to observe outliers in data. Example: sales vs profit:

4

  • Use Histograms to see the data distribution across groups or to display the shape of the sales distribution:

5

Advanced Chart Types:

  • Use Bullet graphs to track progress against a goal, a historical sales performance or other pre-assigned thresholds:

6

  • The Dual-line chart (or dual-axis chart), is an extension of the line chart and allows for more than one measure to be represented with two different axis ranges. Example: revenue vs. expense
  • The Pareto chart is the most important chart in a sales analysis. The Pareto principle is also known as 80-20 rule; i.e roughly 80% of the effects come from 20% of the causes.

7

When performing a sales analysis, this rule is used for detecting the 80% of total sales derived from 20% of the products.

  • Use Box plots to display the distribution of data through their quartiles and to observe the major data outliers

8

Tableau Sales Dashboard

Here is a Tableau dashboard comprised of the aforementioned charts. This interactive dashboard enables the consumer to understand sales information by trend, region, profit and top products.

9

II. Use Actions to filter instead of Quick Filters

Using actions in place of Quick Filters provides a number of benefits. First, the dashboard will load more quickly. Using too many Quick Filters or trying to filter a very large dimension set can slow the load time because Tableau must scan the data to build the filters. The more quick filters enabled on the dashboard, the longer it will take the dashboard to load.

III. Build Cascading Dashboard Designs to Improve Load Speed

By creating a series of four-panel, four cascading dashboards the load speed was improved dramatically and the understandability of the information presented was greatly enhanced. The top-level dashboard provided a summary view, but included filter actions in each of the visualizations that allowed the executive to see data for different regions, products, and sales teams.

IV. Remove All Non-Data-Ink

Remove any text, lines, or shading that doesn’t provide actionable information. Remove redundant facts. Eliminate anything that doesn’t help the audience understand the story contained in the data.

V. Create More Descriptive Titles for Each Data Pane

Adding more descriptive data object titles will make it easier for the audience to interpret the dashboard. For example:

  • Bullet Graph—Sales vs. Budget by Product
  • Sparkline—Sales Trend
  • Cross-tab—Summary by Product Type
  • Scatter Plot—Sales vs. Marketing Expense

VI. Ensure That Each Worksheet Object Fits Its Entire View

When possible, change the graphs fit from “Normal” to “Entire View” so that all data can be displayed at once.

VII. Adding Dynamic Title Content

There is an option to use dynamic content and titles within Tableau. Titles can be customized in a dynamic way so that when a filter option is selected, the title and content will change to reflect the selected value. A dynamic title expresses the current content. For example: if the dashboard title is “Sales 2013” and the user has selected year 2014 from the filter, the title will update to “Sales 2014”.

VIII. Trend Lines and Reference Lines

Visualizing granular data sometimes results in random-looking plots. Trend lines help users interpret data by fitting a straight or curved line that best represents the pattern contained within detailed data plots. Reference lines help to compare the actual plot against targets or to create statistical analyses of the deviation contained in the plot; or the range of values based on fixed or calculated numbers.

IX. Using Maps to Improve Insight

Seeing the data displayed on a map can provide new insights. If an internet connection is not available, Tableau allows a change to locally-rendered offline maps. If the data includes geographic information, we can very easily create a map visualization.

10

This map represents sales by state. The red color represents negative numbers and the green color represents positive numbers.

X. Developing an Ad Hoc Analysis Environment

Tableau facilitates ad hoc analysis in three ways:

  1. Generating new data with forecasts
  2. Designing flexible views using parameters
  3. Changing or creating designs in Tableau Server

XI. Using Filters Wisely

Filters generally improve performance in Tableau. For example, when using a dimension filter to view only the West region, a query is passed to the underlying data source, resulting in information returned for only that region. We can see the sales performance of the particular region in the dashboard. By reducing the amount of data returned, performance improves.

Enhance Visualizations Using Colors, Labels etc.

I. Using colors:

Color is a vital way of understanding and categorizing what we see. We can use color to tell a story about the data, to categorize, to order and to display quantity. Color helps with distinguishing the dimensions. Bright colors pop at us, and light colors recede into the background. We can use color to focus attention on the most relevant parts of the data visualization. We choose color to highlight some elements over others, and use it to convey a message.

Red is used to denote smaller values, and blue or green is used to denote higher values. Red is often seen as a warning color to show the loss or any negative number whereas blue or green is seen as a positive result to show profit and other positive values.

Without colors:

11

With colors:

12

II. Using Labels:

Enable labels to call out marks of interest and to make the view more understandable. Data labels enable comprehension of exact data point values. In Tableau, we can turn on mark labels for marks, selected marks, highlighted marks, minimum and maximum values, or only the line ends.

Without labels:

13

With labels:

14Using Tableau to enhance KPI values

The user-friendly interface allows non-technical users to quickly and easily create customized dashboards. Tableau can connect to nearly any data repository, from MS Excel to Hadoop clusters. As mentioned above, using colors and labels, we can enhance visualization and enhance KPI values. Here are some additional ways by which we can enhance the values especially with Tableau features.

I. Allow for Interactivity

Playing, exploring, and experimenting with the charts is what keeps users engaged. Interactive dashboards enable the audiences to perform basic analytical tasks such as filtering views, drilling down and examining underlying data – all with little training.

II. Custom Shapes to Show KPIs

Tableau shapes and controls can be found in the marks card to the right of the visualization window. There are plenty of options built into Tableau that can be found in the shape palette.

15

Custom shapes are very powerful when telling a story with visualizations in dashboards and reports. We can create unlimited shape combinations to show mark points and create custom formatting. Below is an example that illustrates how we can represent the sales or profit values with a symbolic presentation.

16

Here green arrows indicate good sales progress and red arrows indicate a fall in Year over Year Sales by Category

III. Creating Calculated Fields

Calculated fields can be used to create new dimensions such as segments, or new measures such as ratios. There are many reasons to create calculated fields in Tableau. Here are just a few:

  1. Segmentation of data in new ways on the fly
  2. Adding a new dimension or a new measure before making it a permanent field in the underlying data
  3. Filtering out unwanted results for better analyses
  4. Using the power of parameters, putting the choice in the hands of end users
  5. Calculating ratios across many different variables in Tableau, saving valuable database processing and storage resources

IV. Data-Driven Alerts

With version 10.3, Tableau has introduced a very useful feature: Data-Driven Alerts. We may want to use alerts to notify users or to remind that a certain filter is on and want to be alerted somehow if performance is ever higher or lower than expected. Adding alerts to dashboards can help elicit necessary action by the information consumer. This is an example of a data driven alert that we can set while displaying a dashboard or worksheet.

17

In a Tableau Server dashboard, we can set up automatic mail notifications to a set of recipients when a certain value reaches a specific threshold.

Summary

For an enterprise, a dashboard is a visual tool to help track, monitor and analyze information about the organization. The aim is to enable better decision making.

A key feature of sales dashboards in Tableau is interactivity. Dashboards are not simply a set of reports on a page; they should tell a story about the business. In order to facilitate the decision-making process, interactivity is an important part of assisting the decision-maker to get to the heart of the analysis as quickly as possible.

Author Bio:

Neeru Gupta, Chaitanya Sagar, Prudhvi Sai Ram and Saneesh Veetil contributed to this article.