Reports Dashboards: Custom Insight Example - Pivot Table with Calculated Measure

ProBiz.png

Insights are the individual graphs and charts that make up a Dashboard. You can use and edit our pre-made Insights, or create your own from scratch. If this is your first time using Reports, we recommend starting with the guide linked here.

As an example, let’s build a pivot table that breaks down the sum of opportunity values, the total number of opportunities, and the average value of opportunities per owner in Copper. Here's what that table will look like at the end of this example:

Screen_Shot_2021-04-09_at_2.46.00_PM.png

First, open the Insights Builder by clicking on the Reports icon and selecting Insights Builder.

Screen_Shot_2021-04-06_at_3.50.09_PM.png

Select your Insight type in the top left corner of the Insight Builder. In this case, we’ll select Table.

Screen_Shot_2021-04-12_at_10.56.33_AM.png

Add your measures.  In this case, we’ll use Opp ID and Opp Value. In the data picker, expand the data list under Opportunity. First, drag Opp ID into the Measures section of the Insight Builder. Then do the same with Opp Value.

Screen_Shot_2021-04-09_at_1.35.00_PM.png

Note that the columns in your table will appear in the same order that you added your measures in. So if you added Opp ID first and Opp Value second, those columns will show up in the order in your pivot table.

Once you’ve added the two measures, you’ll see a chart with the total number of opportunities and the sum of their values. Next, we’ll split up those totals by Opportunity Owner.

Add your Rows, which is how your measures are segmented. Go back to the data picker and drag Opp Owner into the Rows section of the Insights Builder.

Screen_Shot_2021-04-09_at_1.44.41_PM.png

Once you’ve dropped the Opp Owner in, you’ll have a table with the number of opportunities and the total value of those opportunities by owner.

Screen_Shot_2021-04-12_at_10.59.58_AM.png

Aggregate your data by hovering over the column header. This allows you to add a final row with the sum, average, or another statistic based on the values in that column.

Screen_Shot_2021-04-09_at_1.55.05_PM.png

The result will look something like this:

Screen_Shot_2021-04-09_at_1.54.36_PM.png

Optionally, you can add a Calculated Measure to calculate the average value of each opportunity for each owner. To do this, go back to the Measures section and click the + button to create a Calculated Measure.

Screen_Shot_2021-04-09_at_2.13.48_PM.png

There, you'll be able to select the type of Calculated Measure (sum, difference, multiplication, ratio, and change. For our example, we’ll use a Ratio to calculate the average opportunity value for each owner.

Screen_Shot_2021-04-09_at_2.25.57_PM.png

Select the input Measures for the ratio calculation.

Screen_Shot_2021-04-09_at_2.26.35_PM.png

You can also rename your Calculated Measure. Here we’ve called it Average Opp Value.

Screen_Shot_2021-04-09_at_2.42.20_PM.png

Once you’ve configured your Calculated Measure, it will be added to your chart as the last column:

Screen_Shot_2021-04-09_at_2.46.00_PM.png

Name your Insight by typing in a new name where it says “Untitled insight.” We recommend giving it a descriptive name, such as “Avg Opp Values by Opp Owner,” so that it’s clear what this Insight represents when you open it up later.

Save your Insight. Remember to click on the Save button in the top right corner.

Screen_Shot_2021-04-09_at_2.35.39_PM.png

Once it’s saved, your Insight will become available to use in the Dashboard editor.

Was this article helpful? 1 out of 1 found this helpful