1. Fraud rates in tables and charts
Let's create a pivot table to display rates of fraud reported by year.
Our pivot table will be a child element to the CALL LOGS table already in our workbook. We'll click the bar chart with the plus sign in the corner of the CALL LOGS table and choose Pivot Table.
Double-clicking the name of the pivot table lets us rename it to Reports of Fraud by year.
Take note that in the pivot table's Properties panel, we can see the name of the pivot table's source - the CALL LOGS table in this case.
Next, we'll drag the appropriate columns onto the Pivot Rows, Pivot Columns, and Values cards.
To show fraud by year we'll drag CALL DATE onto Pivot Rows, truncating to year, and FRAUD FLAGGED onto Pivot Columns.
We'll drag CALL ID onto the Values card and configure it as a Count.
We can layer more than one level of detail in pivot tables - perhaps we'd like to see a breakdown of calls by team for each year. If we drag AGENT TEAM under our YEAR OF CALL DATE column, this allows us to drill up and down in the pivot table.
Each element type has some slight differences to the properties and formatting options depending on what's included in that element. For example in a pivot table, we can change the our data hierarchy to be displayed as a single or separate columns. Here, we can also swap the position of rows and columns.
Next, let's build a bar chart showing the volume of total calls received over time.
Let's go back to the CALL LOGS table and click the bar chart icon again. This time we'll choose chart as a child element.
By default, the type of chart chosen in a bar chart, but in the Properties pane we can change the chart type to any of the many types of charts Sigma can create.
Leaving the chart type set to bar chart for now, we'll double-click the name of the chart and call it Call Volume by year.
Columns can be dragged onto a chart's X and Y axes in the properties menu.
For a time-based X-axis, we'll truncate the Call Date column to display by year, then drag it onto the X-axis section
Next, we'll drag Call ID onto the Y-axis section, noting that it displays as a count of total calls. I'll toggle to the chart's Format tab and turn on Data Labels, so we can see the total counts above each bar.
This is looking better, but perhaps we want to show a breakdown of call volume by team. In the chart properties, dragging the Agent Team column onto the Color card will automatically show this breakdown inside each bar.
We can then hover over different sections of the bars to see information on call volumes by team.
The option to build pivot tables and charts like these will give our end users greater flexibility in how they view summarized data. Instead of sifting through a table of raw data, they can see a concise summary of call activity in an easy-to-understand format.
Now its your turn. In the next exercises you'll build pivot tables and charts to help Oakmark Bank take a closer look specifically at calls related to fraudulent activity.
2. Let's practice!