See the Big Picture with Charts and Get the Details with Tables

Learning Objectives

After completing this unit, you’ll be able to:
  • Explain when to use chart and table widgets.
  • Add chart and table widgets to a dashboard.

Charts and Tables Work Together to Give the Entire Picture

Charts visualize your data so you can quickly get summaries, make comparisons, and identify trends. When you need a close-up view, tables provide you with record-level details. For example, a chart shows the total value of each sales rep’s opportunities, and the table shows details about each opportunity record.

So how do charts and tables work together in Analytics to give you a complete picture of your data? You can select a chart segment and see a filtered list of records in the table. For example, you can select an opportunity type for Irene Kelley in the chart to see only her opportunities of that type in the table.The selection in the chart widget filters the results in the table widget.

We cover more about widget interactivity in an upcoming unit. For now, let’s get back to Laura’s request to be able to compare reps.

Rank Sales Reps and See Who Hit Quota

Laura wants the dashboard to compare sales reps and identify which reps are currently meeting quota. To show how they stack up against each other, create a chart that ranks reps by their total opportunity amount.

Because lost opportunities don’t affect ranking or quota, exclude them from the chart. (Laura can rely on the “Lost Opportunities” number widget to get that information and see the details about each lost opportunity when you add the table.)

Let’s get started.

  1. While in edit mode, drag a chart widget (1) to the dashboard canvas, resize and reposition the widget as shown in the following screenshot, and then click Chart (2).
    The Chart button appears inside the chart widget when you hover over it.
  2. Set the step label to Opp Amount by Owner and Type.
    Analytics reuses the same dataset that was selected when building the previous step.

    Before we continue, notice that you weren’t prompted to select the dataset—the DTC Opportunity dataset was selected for you based on your previous step. Although you’ll use the same dataset, if needed, you can click Back to choose a different one.

  3. Change the measure from count of rows to sum of amount in the Bar Length field.
  4. To group results by sales rep, click + in the Bars field and then select Opportunity Owner.
  5. Add a second grouping based on Opportunity Type.
    Groupings affect each chart type differently. For example, a chart can represent a grouping as a segment or color. Also, if you add multiple groupings, the order of groupings makes a difference. When you have time, play around with the order to see the effects on each chart type. In this case, Opportunity Owner is the first grouping and Opportunity Type is the second.The horizontal bar chart shows the total value of opportunities by opportunity owner and then opportunity type.
  6. To exclude lost opportunities, in the Filters tab, click + under Filters, select Stage, and then select all values, except 09 - Closed Lost.
    Of all possible filter values for the Stage field, all are selected except "09 - Closed Lost."
  7. To apply the filter, click Add.
  8. To rank sales reps by total opportunity amount, in the Data tab, click the down arrow next to the Sum of Amount measure, and then select Sort Descending.
    Tip

    Tip

    You can also click the Sum of Amount axis title to change the sort order.

    The measure menu includes options to sort the results in addition to other things.
    The query ranks the results, but not by sales rep. The chart ranks based on each combination of sales rep and opportunity type. To rank by rep only, let’s use a different chart type.Because there are multiple bars for each rep, it's hard to see total business for each rep.
  9. On the right side of the screen, click the Charts button (Charts button), select Stacked Column, and then click the button again to close the right pane.
    Bar and column charts are excellent visualizations for ranking groups of data based on a single measurement, but only when there’s one grouping. Stacked bar and stacked column charts are better when you have multiple groupings.
  10. Click Formatting button, scroll down in the widget panel, expand the “Y-Axis (Left)” section, and then set the following properties.
    Widget Property Setting
    Title Set to “Total Amount”.
    Reference Line To display the quota line, click + Reference Line and enter 22500000 for the value and Quota $22.5M for the label. When you preview the dashboard, the quota line should appear slightly above the last bar in the chart. If not, verify that you entered the reference line value correctly.
  11. Scroll down a bit more, expand the “X-Axis” section, and then enter Sales Rep for the X-axis title.
    The stacked column chart shows the total value of open and won opportunities grouped by rep and opportunity type. The color coding represents the different opportunity types.
  12. Click Done.
  13. Save the dashboard.

Which Opportunities Are We Talking About?

To switch between the big picture and the details, Laura can go back and forth between Analytics charts and individual Salesforce records. But that’s a little confusing, especially when you can show record details in an Analytics table. With charts and tables in the dashboard, Laura can see both at once.

Let’s add a table to show Laura details about each opportunity.

  1. Drag a table widget from the toolbar to the canvas, increase the size of the table to 12 cells wide by 8 cells tall, and then click Table.
    The "Table" button appears when you hover over the empty table widget.
  2. Set the step label to Opp Record Details.
  3. Click Table Mode button and then select Values Table.
    Compare Table and Pivot Table are some other table options. But we don’t need a compare table (to create a custom column or group rows) or a pivot table. We just want to use a values table to display the dataset fields in a tabular format.
  4. To specify the dataset fields to show as table columns, click Edit button at the bottom of the “Columns” box.
    The values table shows some default columns, which you can change.
  5. Select only the following fields. Remove others.
    • Account Name
    • Amount
    • Close Date
    • Opportunity Name
    • Opportunity Owner
    • Opportunity Type
    • Product Family
    • Stage
  6. Click Update Columns to display the selected fields as the table columns.
    Tip

    Tip

    To change the order of the columns in the table, drag and drop a column inside the Columns box. To sort records based on a column, while previewing the dashboard, click the column header in the table. The table columns appear in the Columns box.

  7. To make more room for other columns, let’s get rid of the # column. Click Formatting button, expand the Other section, and then uncheck the Show Row Index Column option.
    The widget properties appear in the right pane.
  8. Click Done.
    The table appears in the dashboard, but there’s empty space on the right. Let’s fix that in the table widget properties to snugly fit all table columns in that space.
  9. In the Spacing section of the widget properties, select Fit to Widget for Column Width.
    Now the table spans the entire dashboard.
  10. Save the dashboard.

Great work! You now have a table widget that shows the details about each opportunity. You also have a chart widget that ranks the sales reps and shows which reps need more opportunities to meet quota.The table widget shows the details about each opportunity.

Now it’s time to add filters so Laura can analyze sales performance from different angles, like by sales rep, stage, product family, and opportunity amount. We cover this in the next unit.

retargeting