In this video I’ll demonstrate how to use the forecasting analytics option in Power BI. Although Power BI’s forecast algorithm is a black box, it’s more than likely using exponential smoothing to generate results. At a very high level, exponential smoothing is an algorithm that looks for patterns in data and extrapolates that pattern into the future. To help exponential smoothing perform at an optimal level, it is very important to pick an accurate seasonality estimation, as this will have an outsized effect on the time series forecast.
If your data points are at the daily grain, then you’d use 365 as your seasonality value. If your data points are at a monthly grain, then you’d use 12 as your seasonality value. Generally, the more seasonality cycles (e.g., years) that you provide Power BI, the more predictive your forecast will be.
Without giving away the whole video, here is a pro and a con of using forecasting in Power BI.
Con: As I stated earlier the exact algorithm is a black box. Although based upon a Power View blog post, we can reasonably assume exponential smoothing is involved. Furthermore, the results cannot be exported into a spreadsheet and analyzed.
Pro: The ability to “hindcast” allows you to observe if the forecasted values match your actual values. This ability allows you to judge whether the forecast is performing well.
Check out the video; I predict you’ll learn something new.
As always, If you find this type of instruction valuable make sure to subscribe to my Youtube channel.
In this post you’ll learn how to highlight values in your Tableau table using set actions. The dashboard in this video displays the number of total points scored by NBA teams by position in the 2017-2018 season. I will give you step by step instructions on how to implement row and column highlighting on this dataset downloaded from basketballreference.com.
I’ve only made a few minor tweaks but this technique was developed by Tableau Zen Master Matt Chambers. You can check out his blog at sirvizalot.com and follow him at Big shout out to Matt for sharing this technique with the Tableau community!
You can interact with my visualization on Tableau Public:
If you find this type of instruction valuable make sure to subscribe to my Youtube channel!
The default maps in Tableau are just fine but sometimes you need to kick up the flamboyancy factor in your visuals. Integrating maps from Mapbox with Tableau is the perfect way to add some Liberace flash to your development game.
Mapbox is an open source mapping platform for custom designed maps. By creating an account with Mapbox, you can either design your own maps on the platform or use their preset maps, which are all more impressive than the out of the box option in Tableau.
All you need to do is enter your generated API token (provided by Mapbox) into Tableau’s Map Services interface and you’ll have access to some pretty impressive mapping options.
If you’re interested in Business Intelligence & Tableau subscribe to my Youtube channel.
Drilling with Set Actions
If you’ve ever tried to use the default drill functionality within Tableau, you know that it could be a more user friendly experience. The default table drill functionality opens all of the options at the next drill level which can force a user to lose sight of the data upon which they’re focusing. A more user-friendly option enables the user to only drill into a specific selected value where focus and attention can be maintained. This is otherwise known as asymmetric drill down.
Fortunately as of version 2018.3, Tableau has added Set Actions as a new functionality. At a high level, developers can take an existing set and update its values based upon a user’s actions in the visualization. The set can be employed via a calculated field within the visualization, via direct placement in the visualization or on the marks card property.
In lay terms this means empowering a user with more interactivity to impact their analyses.
In this first video, I’ll demonstrate a use of set actions on an NBA data set. We’ll drill from Conference to Division to Team to Player. This tip will be easily applicable to your Tableau data. And with the bonus tree-map tip you’ll release your inner Piet Mondrian.
Feel free to interact with the set action example on Tableau Public and then download and dissect the workbook.
Drilling with Level of Detail (LOD) Calculations
If you want to stay with a classic approach, a nice Level of Detail (LOD) workaround can be employed to drill into the next level. Here is a tip that accomplishes a similar outcome where I demonstrate a technique originally presented by Marc Rueter at Tableau Conference 2017.
Now that I’ve equipped you with the knowledge to incorporate customized drilling functionality into your analyses, go forth and do some great things with your data!
Image Copyright dzxy on 123rf.com
This video represents part two in my Market Basket Analysis series.
The steps in the post were inspired by the book Tableau Unlimited written by former co-worker of mine, Chandraish Sinha. I wasn’t planning to construct another market basket analysis video but when I saw the approach outlined in his book, I felt like it warranted sharing with my readers and followers.
In this version we’ll use default Tableau Superstore data to show the relationship between sub-categories on an Order; all without using a self table join. The visualization and analysis is driven by a user selection parameter.
Once the user selects a sub-category, the bar chart visualization updates to reflect the number of associated sub-category items on the same order.
Watch the video and as always get out there and do some great things with your data!
Feel free to also check out Part 1 here where we create a simpler correlation matrix version that shows all the sub-category relationships in one visual.
A favored analysis technique employed by retailers to help them understand the purchase behavior of their customers is the market basket analysis. When you log on to Amazon, most likely you’ve noticed the “Frequently Bought Together” section where Jeff Bezos and company would like to cross-sell you additional products based upon the purchase history of other people who have purchased the same item.
Market Basket Analysis influences how retailers institute sales promotions, loyalty programs, cross-selling/up-selling and even store layouts.
If a retailer observes that most people who purchase Coca-Cola also purchase a package of Doritos (I know they’re competing companies), then it may not make sense to discount both items at once as the consumer might have purchased the associated item at full price anyhow. Understanding the correlation between products is powerful information.
In this video, we’ll use Tableau Superstore data to perform a simple market basket analysis.
Feel free to interact with this market basket analysis on Tableau Public and then download and dissect the workbook.
Watch the video and as always get out there and do some great things with your data.
Feel free to also check out Part 2 here where we’ll create an analysis driven by a user selection parameter.
SQL is the lifeblood of any data professional. If you can’t leverage SQL and you work with data, your life will be more difficult than it needs to be.
In this video I am using SQL Server Express to turn a simple normalized dataset into a pivoted dataset. This is not a beginner video as I assume you are familiar with basic SQL concepts.
T-SQL is Microsoft’s SQL language that contains additional functions and capabilities over and above ANSI standards. We’ll use some of these functions to turn the following data set that displays average rents in major American cities into a pivoted denormalized dataset.
The City values in the City column will become individual columns in a new pivoted dataset with their respective Average Rent values appearing underneath.
We’re going to transform this:
Notice how the city values are now column heads and the respective Average Rent values are underneath.
Make sure you watch the video but here is the code used in the example.
IF OBJECT_ID('tempdb..##TBL_TEMP') IS NOT NULL
DROP TABLE ##TBL_TEMP
--This parameter will hold the dynamically created SQL script
DECLARE @SQLQuery AS NVARCHAR(MAX)
--This parameter will hold the Pivoted Column values
DECLARE @PivotColumns AS NVARCHAR(MAX)
SELECT @PivotColumns= COALESCE(@PivotColumns + ',','') + QUOTENAME([City])
/* UNCOMMENT TO SEE THE NEW COLUMN NAMES THAT WILL BE CREATED */
--Create the dynamic query with all the values for
--pivot column at runtime
--LIST ALL FILEDS EXCEPT PIVOT COLUMN
SET @SQLQuery =
N'SELECT [City Code],[Metro],[County],[State],[Population Rank],' + @PivotColumns + '
PIVOT( MAX([Average Rent])
FOR [City] IN (' + @PivotColumns + ')) AS Q'
/* UNCOMMENT TO SEE THE DYNAMICALLY CREATED SQL STATEMENT */
--Execute dynamic query
EXEC sp_executesql @SQLQuery
/* VIEW PIVOTED TABLE RESULTS */
Select * from ##TBL_TEMP
Big shoutout to StackOverflow for help with this example.