Once you have connected to a tabular model using either a new or existing connection (read article: How to connect to tabular models in Excel), a dialog box will appear prompting you to import the data into one of your Excel worksheets in one of a handful of formats. The simplest way to get started is to select "PivotTable Report" for how you want to view the data, then click "OK."
At this point, you should now see a worksheet with a blank PivotTable, and on the right side of your screen you should see a new pane has opened called "PivotTable Fields" that displays a list of objects in the tabular model.
In this example, we want to show Davidson's admission statistics over a period of years. To accomplish this, we will drag a few fields from the tabular model into our Excel PivotTable, and the data will appear and calculate automatically as we update our report.
Let's get started by dragging the "Admission Cycle" hierarchy into the Rows section of our PivotTable (read article: Hierarchies Explained).
In the PivotTable Fields, find the "Applicant" table and expand it so you can see the "Admission Cycle" hierarchy, then expand the hierarchy so you can see its component parts, which in this example are "Admission Year" and "Round."
You can either click on the select box next to "Admission Cycle" to add the hierarchy to your PivotTable row groups, or you can drag and drop it into the row groups.
At this point, your PivotTable should have updated to display all available Admission Years. To expand a given year to see the Rounds within it, click on the plus (+) symbol next to the year.
Next, we're going to add a few Measures from our tabular model into the "Values" section of the PivotTable (read article: Measures Explained).
To accomplish this, find the "Application" measures within your PivotTable Fields — don't forget to look for the sigma symbol (Σ)! — and expand it so you can see the measures.
Find the following measures and drag-and-drop them into the Values section of your PivotTable in this order:
- Total Applicants
- Enroll
- Acceptance Rate
- Yield
Your PivotTable groupings should now look like this:
And your PivotTable should now look like this:
You can see we've got some years in the Admission Cycle hierarchy that don't make much sense. We have 809 applicants with a blank year, one with a zero for the year, one from 1983 somehow, and then only a dozen applicants for 1993. After 1993, the data looks very consistent, until we get to 2019 when we've already got some applicants, but nobody is enrolled yet, so there is no Acceptance Rate or Yield to report on.
What we'll do to make this report more presentable is filter these years out of the PivotTable so they don't muddy up everything. To accomplish this, Ctrl+Click each of the years in question inside your PivotTable, then right-click > "Filter" > "Hide Selected Items."
Now your PivotTable is filtered and looks much more presentable.
However, it's not necessarily easy to tell from this PivotTable how these different data series are trending over time, so let's create a visualization. To do this, we can just build a PivotChart, which is an Excel chart that is directly tied to the data in your PivotTable, and will update dynamically as you update your PivotTable with new fields, or if you expand any row group hierarchies.
To add a PivotChart to your worksheet, click anywhere in the PivotTable to expose the "PivotTable Tools" in your main ribbon, then click into the "Analyze" tab and select "PivotChart."
Now you should see a dialog box prompting you to select what kind of PivotChart you want to build. The dialog also displays a preview of the chart you're about to build, and allows you to modify how your data series are presented in the chart. In this example, we're going to build a combo chart, which is a chart that has two vertical (Y) axes.
Why build a combo chart with two Y-axes? Because we're looking at four measures that are totally different in scale: "Total Applicants" and "Enroll" are both represented by whole numbers starting at zero and going up into the thousands, whereas "Acceptance Rate" and "Yield" are represented by percentages starting at 0.00% and going up to (ideally) a max of 100.00%. The whole number equivalent of 100% is 1 (one) so if you were to put percentage measures on the same axis as your whole number measures, you very likely won't be able to see them well because they will all be clustered between 0 and 1 on your Y-axis, while all the rest of your values will be soaring up into the thousands. If you look at the screenshot above, the preview is giving you an example of this issue and how your chart would look.
To get around this issue, we're going to move our percentage measures ("Acceptance Rate" and "Yield") onto the secondary axis of our combo chart. At the bottom of the dialog box, you should see a box listing each data series in your chart, plus the chart type, and then a checkbox for each allowing you to move the data series onto a secondary axis. Click the "Secondary Axis" checkbox for both "Acceptance Rate" and "Yield." As you do this, the chart preview will update showing you how these two measures will appear once they have shifted onto the secondary axis.
Now it's time to hit the "OK" button and build your chart. The chart dialog box will disappear, and a new PivotChart will show up in your worksheet next to your PivotTable. Resize it as necessary so you can see it better, and now you've got a great visualization showing you how these four measures have changed over time for Davidson College. In particular, it's easy to see the large increase in applications to Davidson over the previous 24 years, alongside a corresponding decrease in the Acceptance Rate while the Yield more or less holds steady around 45%. This kind of time-series analysis of trends is easy to perform, and just a few clicks away, thanks to Davidson's Enterprise Data Warehouse.
As mentioned earlier, if you update any attribute of your PivotTable such as expanding a hierarchy or changing your filters, your PivotChart will update automatically. However, if you add or remove any fields, it's very likely that you will need to rebuild your PivotChart because it won't necessarily know how to organize the primary and secondary axes with the changed information. If you ever need to rebuild your PivotChart due to changed information that messes up your axes, simply click on the chart to expose the "PivotChart Tools" tab in your Excel ribbon, then click the "Design" tab and select "Change Chart Type."
Making your report interactive with slicers
Excel has a great feature that is shared with Power BI, called "Slicers." These allow you to filter the data in your report much faster than you otherwise might using the traditional filter controls in Excel.
To add a slicer to this report, click anywhere inside your PivotTable to expose the PivotTable Tools tab in your ribbon, then go to the Analyze tab and select "Insert Slicer."
A dialog box will appear asking you to select which field to use for the slicer, and you'll select Applicant > Admission Cycle > Admission Year, then click "OK."
The slicer will appear in the body of your worksheet, and clicking/un-clicking items in the slicer list will cause your PivotTable and PivotChart to update their data.
