www.dataplusscience.com Open in urlscan Pro
209.17.116.160  Public Scan

Form analysis
1 forms found in the DOM

Name: mc-embedded-subscribe-form — POST https://bigbookofdashboards.us14.list-manage.com/subscribe/post?u=b933b1c23a8aec9e837dc6663&id=3c9aab18df&f_id=002bb0e5f0

<form action="https://bigbookofdashboards.us14.list-manage.com/subscribe/post?u=b933b1c23a8aec9e837dc6663&amp;id=3c9aab18df&amp;f_id=002bb0e5f0" method="post" id="mc-embedded-subscribe-form" name="mc-embedded-subscribe-form" class="validate"
  target="_blank" novalidate="">
  <h4>Subscribe</h4>
  <p>Enter email to subscribe and <br>receive periodic notifications. </p>
  <div id="mc_embed_signup_scroll">
    <input type="email" value="" name="EMAIL" class="email" id="mce-EMAIL" placeholder="email address" required="" style="color:#1277BD">
    <div style="position: absolute; left: -5000px;" aria-hidden="true"><input type="text" name="b_b933b1c23a8aec9e837dc6663_3c9aab18df" tabindex="-1" value=""></div>
    <p></p>
    <div class="email-newsletter"><input type="submit" value="Subscribe" name="subscribe" id="mc-embedded-subscribe" class="btn btn-blue"></div>
  </div>
</form>

Text Content

Data Plus Science, LLC | contact@dataplusscience.com

 * Home
 * About
 * Services
 * Contact

blog


5/13/2020
Five Solutions to Highlight an Entire Table Row in Tableau

This post started as a question from one of my students at the University of
Cincinnati. He wrote, "I am required to colour the entire row in a text table
instead of just the measure values so that the user can easily spot the
different entities (or institutional groups in this case). Is there a way to
colour code the entire row so that the user can easily distinguish the different
institutions?". When dealing with tables, things that are simple in tools like
Excel can be much more difficult in Tableau. In Excel, you can highlight any row
you want and with a simple click you can set a highlight color for the entire
row. You will see that to do this in Tableau requires some creative solutions. I
will present five different solutions in this post, but each of these solutions
has its pros and cons.

This turned into a really long blog post, so if you just want the best solution,
then you can simply jump down to Solution #5. This solution requires a tricky
calculation, but in my opinion, while the other solutions work, Solution #5
seems to be the best solution and the most robust.

First, let's start with the goal. Here's the table that we are trying to create.



If you've used Tableau for any length of time, you will understand the challenge
already. While it's easy to highlight the values themselves (like a highlight
table would), it's not easy to highlight the entire row.

If you want to follow along with these instructions then download this file here
and import into Tableau. You can also download the Tableau workbook heree.

Note - I will use a parameter in the first four solutions, which we will use to
select the row that we want to highlight. In Solution 5, I will use a Set so
that we can highlight multiple rows.

Create a parameter for the user to select the row to highlight.

   Parameter: Row Select
   Data Type: Integer
   Allowable Values: List
   Add values from Row ID (or Enter the values 1 to 10 in the list box) and
click OK




SOLUTION 1 - CREATE SPACERS


We can concatenate all the text fields into one text field, by stringing them
together on Text on the Marks Card. Then when the text is highlighted it will
highlight that entire row. This requires creating a spacer calculation for
between each value to give the appearance of columns. To make this easier and
clean, we will use Courier New because it is a monospaced font (each letter is
the same width) and it is web safe.

We will create the calculated fields for the spacers using the SPACE() function
in Tableau:

   Calculated Field: Spacer 1
   Formula:
   space(50 - len([Institution Name]))

   Calculated Field: Spacer 2
   Formula:
   space(20-len([Institution Type]))

   Calculated Field: Spacer 3
   Formula:
   space(10)

   Calculated Field: Row Select Color
   Formula:
   [Row Select] = [Row ID]

Building the table now is pretty straight forward.

   Double-click Row ID to add it to Rows.
   Drag Row ID to Text on the Marks Card.
   Drag Spacer 1 to Text on the Marks Card.
   Drag Institution Name to Text on the Marks Card.
   Drag Spacer 2 to Text on the Marks Card.
   Drag Institution Type to Text on the Marks Card.
   Drag Spacer 3 to Text on the Marks Card.
   Drag Expenditures to Text on the Marks Card.

Formatting the text box on Text on the Marks Card is critical. We need to do
three things.

First, put all the fields on one row in the right order. Click on Text on the
Marks Card and click the "..." to edit the text. Delete all of the line breaks
and spaces and make sure that Sum(Expenditures) is at the end of the list.
Second, right-align the text by clicking the right-aligned button.
Third, select all of the text in the text box and change the font to Courier
New.

The Edit label box should look like this (if it does then click OK):




"Fit Width" or manually size the table wider and you will see that you now have
a table with the columns and they are properly spaced and aligned.

   Drag Row Select Color to Color on the Marks Card.
   Double-click the color legend and set the False to White and True to a
highlight color. I chose a yellow color with 80% Opacity.
   Change the Mark Type in the dropdown box to Gantt.
   Double-click in the blank area at the bottom of the Marks Card to type in an
inline formula.
   Type avg(1) (note - you can put any value in parenthesis).
   Drag this new pill AGG(avg(1)) to Size on the Marks Card.
   Click Size on the Marks Card and drag the slider all the way to the right.
   Right-click on Row ID on the Rows and uncheck "Show Header".
   Right-click on the parameter Row Select and select "Show Parameter".
   Select 6 in the Row Select parameter dropdown.

You should now have a table that that looks like this.



The pros for this solution is that you could have a multi-highlight using a set
instead of a parameter and you have control over the spacing between each
column. The cons are being forced to use a monospaced font and having to create
a calculated spacer field between each column. If you want to change column
order then you will need to adjust the spacer calculations.




SOLUTION 2 - MULTIPLE MARKS CARD


After coming up with a few ways to solve this, I forwarded this little challenge
around the office and Kevin Flerlage came back with this solution. Create
multiple Marks Cards using dummy values.

We need the same calculated field that we used in Solution 1 for this one.

   Calculated Field: Row Select Color
   Formula:
   [Row Select] = [Row ID]

Create four dummy values on Columns.
   Double-click Row ID to add it to Rows.

Repeat this next step for each column you need.
   Double-click in the blank area on Columns to type in an inline formula.
   Type min(-1) and hit enter.
   Double-click in the blank area on Columns to type in an inline formula.
   Type min(-1) and hit enter.
   Double-click in the blank area on Columns to type in an inline formula.
   Type min(-1) and hit enter.
   Double-click in the blank area on Columns to type in an inline formula.
   Type min(-1) and hit enter.

Marks Card #1
   Drag Row ID to Text on the Marks Card.

Marks Card #2
   Drag Institutional Name to Text on the Marks Card.

Marks Card #3
   Drag Institutional Type to Text on the Marks Card.

Marks Card #4
   Drag Expenditures to Text on the Marks Card.

All Marks Card
   Drag Row Select Color to Color on the Marks Card.
   Double-click the color legend and set the False to White and True to a
highlight color. I chose a yellow color with 80% Opacity.
   Click on Label on the All Marks Card and set the Alignment to "Left".

For all four columns
   Double-click on the X-Axis of each column and set to Fixed from -1 to 0
   Right-click on the X-Axis and uncheck "Show Header".
   Right-click on Row ID on the Rows and uncheck "Show Header".
   Right-click on chart area and select Format to format the Row Divider and
Column Divider Borders and zero lines as needed.

You should now have a table that looks something like this:



The pros for this solution is that you can highlight multiple rows using a set
instead of a parameter and you have more control over the font choice. The
biggest con is that the column width is fixed, so you lose the flexibility and
control over the column spacing.




SOLUTION 3 - MULTIPLE MARKS CARD WITH VARIABLE WIDTH COLUMNS (SORT OF)


This one is a variation on Kevin's solution. We do the exact same steps, except
we set the dummy values to different values and make them a dual-axis so that we
can have some control over the column width. The primary axis is a Gantt Bar
mark which creates the highlight and the secondary axis is a square, which is
used for control over the label placement and alignment.

We need the same calculated field that we used in Solution 1 and 2 for this one.

   Calculated Field: Row Select Color
   Formula:
   [Row Select] = [Row ID]

Create four dummy values on Columns.
   Double-click Row ID to add it to Rows.
Repeat this next step for each column you need.
   Double-click in the blank area on Columns to type in an inline formula.
   Type min(0) and hit enter.
   Double-click in the blank area on Columns to type in an inline formula.
   Type min(.6) and hit enter.
   Double-click in the blank area on Columns to type in an inline formula.
   Type min(0.0) and hit enter.
   Double-click in the blank area on Columns to type in an inline formula.
   Type min(.9) and hit enter.

Marks Card #1
   Drag Row ID to Text on the Marks Card.
   Change the mark type in the dropdown box on the Marks Card to Gantt Bar.
   Double-click on the area at the bottom of the Marks Card to type and inline
formula.
   Type sum(2) and hit enter.
   Select Size on the Marks Card and drag the slider all the way to the right.

Marks Card #2
   Drag Institutional Name to Text on the Marks Card.
   Change the mark type in the dropdown box on the Marks Card to Square.
   Right-click on this new pill and select "Dual Axis".
   Right-click on the secondary X-Axis and select "Syncronize Axis".

Marks Card #3
   Drag Institutional Type to Text on the Marks Card.
   Double-click on the area at the bottom of the Marks Card to type and inline
formula.
   Type sum(1) and hit enter.
   Select Size on the Marks Card and drag the slider all the way to the right.

Marks Card #4
   Drag Expenditures to Text on the Marks Card.
   Change the mark type in the dropdown box on the Marks Card to Square.
   Click on text on the Marks Card and set the Alignment to Left.
   Right-click on this new pill and select "Dual Axis".
   Right-click on the secondary X-Axis and select "Syncronize Axis".

All Marks Card
   Drag Row Select Color to Color on the Marks Card.
   Double-click the color legend and set the False to White and True to a
highlight color. I chose a yellow color with 80% Opacity.

Additional Formatting
   Double-click on the X-Axis min(0) and set to Fixed from 0 to 2
   Double-click on the X-Axis min(0.0) and set to Fixed from 0 to 1.5
   Right-click on the X-Axis and uncheck "Show Header".
   Right-click on Row ID on the Rows and uncheck "Show Header".
   Right-click on chart area and select Format to format the Row Divider and
Column Divider Borders as needed.

You should now have a table that looks something like this:



The pros for this solution is that you can highlight multiple rows using a set
instead of a parameter and you have more control over the font choice. The con
is that you will have to adjust the dummy value, the fixed axis range and the
text alignment to control the spacing.




SOLUTION 4 - ROW BANDING WITH PARAMETER (SINGLE ROW HIGHLIGHT - SORT OF)


This is an easy one and a fun trick, but it will only work when you need to
highlight a single row, or when you can group the highlighted rows together.
This is because we are going to leverage Row Banding in the format pane, which
will alternate rows or groups of rows when applying the row banding. It's also
the easiest to build because it just requires just one calculated field for the
row banding order.

In this solution we will not use the calculated field on color that we used in
the other solutions. In this solution we need a different calculation to group
the rows together. Any Row ID that is less than the parameter will be 1, if it's
equal to the parameter then 2 and if it's greater than the parameter then it
will be a 3.

   Calculated Field: Row Band Order
   Formula:
   if [Row ID] < [Row Select] then 1
   elseif [Row ID] = [Row Select] then 2
   else 3
   end

   Right-click the new calculated field Row Band Order and select "Convert to
Dimension" (or drag the pill from Measures to Dimensions).

Building the table for this solution is the easiest, because it's the standard
table layout in this solution.

   Double-click Row Band Order to add it to Rows.
   Double-click Row ID to add it to Rows. (Note - this should be discrete)
   Double-click Institution Name to add it to Rows.
   Double-click Institution Type to add it to Rows.
   Double-click Expenditures to add it to Text.
   Select Format from the top menu and Shading to open the Format Shading pane.
   Change the Row Banding Pane and Header to the desired highlight color.
   Move the Row Banding Level slider all the way to the left.
   Right-click on Row Band Order on the Rows and uncheck "Show Header".

The pros for this solution is that it's very easy to implement because you set
it up in a typical table format. The con is that you can only select one row at
a time. And the biggest drawback? Try selecting row 1. Unfortunately, there is
no way to control how the row banding starts. So, this requires one more trick
if you need to highlight the first row. Note - This trick is manual and only
highlights the first row correctly. Therefore, another con for this solution,
while it would work great for a static visualization, it's not a dynamic
solution that would allow the user to pick a row or set of rows.

To highlight the first row, you simply add totals to the top of your table and
then the row banding will start on the totals line instead of the first row of
the data.

   Select "Analysis" from the top menu and select "Totals" then select "Show
Column Grand Totals".
   Select "Analysis" from the top menu and select "Totals" then select "Column
Totals to Top".
   Select "Format" from the top menu and select "Font" to open the Format Font
pane and select the "Sheet" tab.
   Change the Total Pane and Header font color for the sheet to match the
background color (ex. white) to make them disappear.

Again, not a great solution unless you have a static visualization, but it was
worth mentioning. Let's move on to Solution 5, which is my favorite solution.




SOLUTION 5 - ROW BANDING WITH SET (MULTIPLE ROW HIGHLIGHT)


We need to solve two problems from Solution 4. First, we will add a dummy row in
the data with Row ID = 0. By doing this, we can have complete control over the
Row Banding and we can start the highlight in Row 1 if we need to or another
other row as needed. This is pretty easy. We can simply add the row to the data
set, join it up with another file to create a dummy row (using Tableau Desktop
or Prep or custom SQL) or we can bring a dummy row in with our data. Regardless
of the method, we simply need a dummy record with blank values and Row ID = 0.

The bigger issue that we need to solve is being able to highlight multiple rows,
for example, we want to highlight any rows that are included in a set. To do
this, we need to create a set with the rows we want and a calculated field that
is a bit tricky.

If you want to follow along with these instructions then download this file
here.

   Import the data in with the dummy record. In this case our 10 records now
have 11 rows with the first record in the table being Row ID = 0.
   Right-click on Row ID and select "Create" and select "Set". This will create
a set for Row IDs. You should see Row IDs from 0 to 10. Select a few of them at
random (other than 0).

Now we create a new calculated field. This one is a bit tricky, but in a
nutshell, we are simply grouping the rows together, alternating between not
highlighting and highlighting. Here's the logic.

If it's the dummy row, then we set it to 0 (the first group of IDs).
If the previous row is not in the set and the current row is in the set then we
increment to the next number (starting the counter for the highlighted rows).
If the previous row is in the set and the current row is not in the set then we
increment to the next number (starting the counter for the non-highlighted
rows).
Finally, if none of those are true, then we use the value from the previous row.

Whew, that's a bunch of checks, but basically, we are just trying to group up
the highlighted rows and the non-highlighted rows down the table, starting with
a non-highlighted row (the dummy row).

   Calculated Field: Color Banding SET
   Formula:
   if min([Row ID])=0 then min([Row ID])
   elseif zn(lookup(avg(int([Set 1])),-1)) = 0 and zn(lookup(avg(int([Set
1])),0)) = 1 then min([Row ID])
   elseif zn(lookup(avg(int([Set 1])),-1)) = 1 and zn(lookup(avg(int([Set
1])),0)) = 0 then min([Row ID])
   else PREVIOUS_VALUE(1)
   end

   Right-click the new calculated field Color Banding Set and select "Convert to
Dimension" (or drag the pill from Measures to Dimensions).

   Drag Color Banding Set to Rows.
   Drag Row ID to Rows.
   Drag Institution Name to Rows.
   Drag Institution Type to Rows.
   Double-click Expenditures to add it to Text.
   Right-click on Color Banding Set on Rows and select "Compute Using" and
select "Table Down".
   Select "Format" from the top menu and select "Shading" to open the Shading
format pane and select the "Sheet" tab.
   Change the Row Banding Pane and Header for the sheet to the desired highlight
color.
   Move the Row Banding Level slider all the way to the left.
   Right-click on Color Banding Set on the Rows and uncheck "Show Header".
   Right-click on the zero on the dummy row and select "Edit Alias". Type in a
space and hit enter.
   Right-click on the NULLs on the dummy row and select "Edit Alias". Type in a
space and hit enter.

You should now a table that looks like this.




The pros for this solution is that it's pretty easy to implement because you set
it up in a typical table format and there's only a single calculation. You can
also highlight multiple rows using a set, so the user could have control of
what's in or out of the set to highlight. The only drawbacks to this approach
are 1.) you need to add a dummy record to the data and 2.) there will be blank
row on the top of the table. I don't think either of these are a big deal, so
this should be fairly easy to implement.

Since we have a dummy row and an empty row in our table, let's leverage this to
highlight all the rows. We can alias the zero in the dummy row to "All" and make
a slight adjustment to the Color Banding Set calculation.

   Right-click on Color Banding SET and select "Edit".

   Calculated Field: Color Banding SET
   Formula:
   if min([Row ID]) = 0 and min([Set 1]) = TRUE then 0
   elseif zn(lookup(avg(int([Set 1])),first())) = 1 then 1
   elseif min([Row ID])=0 then min([Row ID])
   elseif zn(lookup(avg(int([Set 1])),-1)) = 0 and zn(lookup(avg(int([Set
1])),0)) = 1 then min([Row ID])
   elseif zn(lookup(avg(int([Set 1])),-1)) = 1 and zn(lookup(avg(int([Set
1])),0)) = 0 then min([Row ID])
   else PREVIOUS_VALUE(1)
   end

Finally, if you combine this solution with the power of the new Tableau 2020.2
feature Set Control, the user will be able to quickly and easily change the set
to highlight whatever rows in the table they wish to highlight. Be sure to check
out this blog post on Tableau 2020.2 Set Controls by Kevin Flerlage to see the
various ways you can use this new feature.

For example:

   Right-click on Set 1 and select "Show Set". This will give the user the
ability to change the Set values.
   Follow Kevin's instructions for creating a worksheet Set Action to Add and
Clear All of the values.

Now the user can click on any row of the table to add it to the set, which will
highlight the rows. And by clearing the selection, the user can clear the
highlighting for the entire table. This works in combination to the Show Set
controls. This can be a very powerful feature.



Below is a Tableau Public Visualization with all of these examples and few more
variations. Feel free to download the Tableau workbook to explore these
solutions in further detail.





I hope you find this information useful. If you have any questions feel free to
email me at Jeff@DataPlusScience.com

Jeffrey A. Shaffer
Follow on Twitter @HighVizAbility






COMPANY

Home About DPS Services Contact Blog

STAY CONNECTED

Connect with us

SUBSCRIBE

Enter email to subscribe and
receive periodic notifications.



CONTACT

contact@dataplusscience.com
Data Plus Science, LLC
Cincinnati, Ohio
United States


Email us
© Copyright 2015-2019 Data Plus Science, LLC. All rights reserved.