Exercise: Sales by Category, Region, Segment, or Ship Mode

Create a bar chart controlled by your end users

In this self-guided exercise, you will create a bar chart that looks at sales across four different dimensions. The catch is that your end users get to choose which dimension drives the analysis!

Hi, this is Ryan with Playfair Data TV. And in this exercise, we’re going to use a parameter control to allow our end user to choose from four different dimensions. We’re going to allow our end user to choose from Category, Region, Segment, and Ship Mode.

Once you’ve created the calculated dimension, you’re just going to make a bar chart. The measure that we care about is Sales. And we’re going to slice and dice that bar chart by our newly created Dim Selected parameter.

Take a shot at that. Pause the video. See if you can figure it out using the Sample Superstore data set. When you’re ready to see how it was created, click play, and I’ll come back and help you out.

All right, the first step to create this parameterized bar chart is to create a parameter. I like to create a parameter by right clicking in any blank space in the Data pane. If you don’t see any blank space, you can also click this down arrow in the top right corner of the Dimensions area of the Data pane. The second choice is Create Parameter.

I’ll give this a name. The name doesn’t matter too much. As long as you remember it and can find it, it’s a good name. But I like to precede my parameters with p dot, which tells me that it’s a parameter. And I’ll call this p dot Dim Selected.

We’re allowing our end user to choose from four different words– Category, Segment, Region, and Ship Mode. So because we’re choosing four different words, the data type is String. We also have a very specific list of words. So I’ll choose List for my allowable values. And I will type out each one of those dimension names. So Category is the first one, Region, Segment, and Ship Mode.

It’s very important to note exactly how these are spelled, including casing. So if it helps you, you could write these down or take a screenshot of it, because we will need to reference these later on in the next step. I’m going to click OK.

Parameters do almost nothing on their own. If I try to drag this onto the view, it’s not even letting me drop it anywhere. I need to use this integrated within a calculated field to give Tableau instructions for what to do with each of those four selections.

So I need to create a calculated field. I’ll call this one c dot Dim Selected just to specify and separate the calculated field versus the parameter version of Dim Selected. And the formula is CASE, whatever parameter I am referencing, so p dot Dim Selected. When it turns purple, that means Tableau recognized it as a parameter in my data source.

Next line is WHEN quote and I’m going to type the first allowable value within quotes. And the reason that allowable value is within quotation marks is because the data type is String. What comes next is the word “THEN” and then whatever I want Tableau to display when that allowable value of Category is selected. In this case, I want Tableau to display the Category dimension. If it turns orange, that means Tableau recognized that as a field in my data set. So so far so good.

And on that note, case when logic always concludes with the word “END”. So after I type the first line of logic, I like to go ahead and type the word “END”. This is just a quick spot check to make sure I’m on the right track. I would rather know if I’ve got an error so far versus typing out 10 different choices and then finding out that I have an error. But so far so good.

So I’ll go ahead and go back to the other three allowable values– when Region then Region, when Segment then Segment, and when Ship Mode then Ship Mode. And that is the entire calculated field. I’m going to click OK.

Notice that the calculated field got placed here as a dimension. It’s been classified as a dimension, because every single outcome is a dimension. But now that I have that newly created calculated dimension, I can finish the exercise and just simply make a bar chart. The measure that we cared about was Sales. So I’ll double click Sales to add that to the Rows Shelf. And I will slice and dice it by that newly created Dim Selected calculated field. I’ll also go ahead and sort this in descending order, which is usually a better practice for a bar chart. And so far so good.

Notice that right now we’re looking at Sales by Category. And that is because Category was the first allowable value in our parameter. And you can confirm it’s the current value by right clicking on the parameter and clicking Edit. And there’s a box here that says Current Value is Category. So that’s what we’re visualizing by default when we first created this.

But there’s one important step. If you want to unlock the ability for your end user to choose what allowable value is slicing and dicing that sales amount by, you need to right click on the parameter and click Show Parameter Control. You’ll see it show up here in the top right corner.

This dropdown will contain your four choices. And as you make different selections, you’ll see that the sales amount is being broken down by different dimensions. There’s Sales by Region. You can confirm that by looking at the dimension members. There’s Sales by Segment. And there’s Sales by Ship Mode.

One last thing for extra points. Unless you look at the selection in the parameter control, you don’t necessarily know what this is being broken down by. You can add the parameter allowable value name as the axis title by dragging it onto the Columns or Rows Shelf.

This time, I’m breaking this sales amount down vertically into columns. So I’m going to drag that p dot Dim Selected parameter to the Columns Shelf and let go. And I put that on there as the first thing. Now you can see the dimensional breakdown as part of the axis title.

I’ll go ahead and hide this first part of it by right clicking and clicking Hide Field Label for Columns. But now when I make the other three choices, that is the word being displayed at the top of the bar chart.

This has been Ryan with Playfair Data TV – thanks for watching!