How to Align Values to a Visualization Using a Table with Custom Formatting

Sometimes you may want to add context to data shown in a visualization by including additional metrics, such as Sample Size or Mean, for each category. You can show these values for each bar or column in a visualization by aligning the values shown in a table using a Table with Custom Formatting or by using the CreateCustomTable() function in a custom Calculation. A couple common examples are:

• You have a chart showing the breakdown of ratings for each brand, you want to show the Mean score for each brand next to each bar/column. 
• You want to add the Count or Sample Size to each bar/column in a visualization. 

This article describes how to create a Table with Custom Formatting (also known as an Autofit or CreateCustomTable) to align sample sizes to bars on a visualization like in the picture below.

There are two methods and some notes worth reviewing:

• Method - Using the Visualization menu
• Method - Using R code
• Additional notes

Requirements

• A visualization. This is mostly used alongside Bar, Column, Stacked Bar, and Stacked Column visualizations.

Method - Using the Visualization menu

If you don't plan on do any dynamic coloring or formatting, you can use the Visualization > Specialty Tables > Table with Custom Formatting output.

1. Create a table that shows the means/values that you want to add to each bar/column. You may simply need to Duplicate the variable set and change the Properties > Structure to Numeric in order to create a table of Averages. Or depending on what you need, you could Duplicate of your input table and changing the Properties > Statistics > Cells to whatever calculation you want.
2. From the Properties on the table that shows the data, select Visualization > Specialty Tables > Table with Custom Formatting to convert it to a custom table.
3. Optional: Use the Properties > Data tab to select which rows/columns/order to take the values from, if needed.
4. Optional: Transpose the values from being shown horizontal to vertical or vice versa by checking Properties > Chart > Cell Values > Swap rows and columns.
5. On the Chart tab, uncheck Row Header > Show rows headers and/or Column Header > Show column headers to only show the values.
6. Also uncheck Scrollbars > Horizontal and Vertical to remove any scrollbars that might appear.
7. Resize the output to align the values to their corresponding bar/column in the chart.
8. Optional: There are other formatting customizations in Properties > Chart that you can do, such as adding a prefix or suffix and setting the font.

Method - Using R code

If you want to do advanced formatting on the table with dynamic coloring or need more customization options, you can also create the Table with Custom Formatting using CreateCustomTable() in R. This example uses the summary table below showing the Count for each preferred cola. 

1. Create a table that shows the means/values that you want to add to each bar/column, like the table above. This can be as simple as creating a Duplicate of your input table and changing the Properties > Statistics > Cells to whatever calculation you want.
2. From the Report tree inserter (+) menu or toolbar, select Calculation > Custom Code. If working on a Page, click to position the output on the Page.

3. Paste the code below into the Code panel:

   #STEP 1:
   #replace table.Q3.Preferred.cola below with the table/row/column reference showing the values you want aligned
   #note you can simply click on the row/column header while your cursor is in the code to insert
   n = table.Q3.Preferred.cola
   
   #Step 2:
   #add a prefix to each count - can modify text inside "" or add a suffix instead by adding ,"" inside the function
   n = paste0("n=",n) # Add 'n=' to each value
   
   #Step 3:
   # Create the custom table that can resize the data to align with the column visualization
   # change transpose = F below if you want a column of numbers
   flipFormat::CreateCustomTable(n,
                               font.size=10,
                               transpose = T)

4. Re-position and re-size your custom table by dragging until it lines up with your visualization with the correct spacing.
5. Optional: Further customize your table using other arguments listed in CreateCustomTable

Additional notes

Keep in mind, the resulting Table with Custom Formatting output is a table created using HTML and does not export to PowerPoint as editable text. If you need editable text in PowerPoint and the number of bars/columns does not change, you can also create individual Calculation > Custom Code outputs for each bar/column that reference individual cells on the table (just click on the cell while inside the Code panel). Note that because PowerPoint charts have different defaults and margins than Displayr, you may need to make small adjustments to the positioning of the text boxes in the PowerPoint export.

Next 

How to Select the Type of Table that is Right for You

How to Create a CreateCustomTable R Table of Images

How to Customize Colors on a CreateCustomTable R Table

How to Customize Fonts in a CreateCustomTable R Table

How to Use R in Displayr

How to Add Customized Footers and Sample Size Widgets