Add more context with pivots

In this section

Getting started with pivots

Make sure you’ve entered “chart mode” and created a simple grouping from student roster data similar to the section Summarize data with groups and charts. If not, do so now. You should see a screen similar to this:

See the the Pivot Mode toggle at the top of the tool panel? Click it. You’ll see a few minor changes to the page. The “filter row” in the data disappears as do the group chevrons to the left of the grade levels in the synthetic column of grouped grade data. Additionally, Grade Level is checked in the tool panel and a new section called Column Labels appears at the bottom of the tool panel. Your page should look something like this:

Remember our question…

What are our attendance rates by grade, race, and ethnic group?

With our current grouping we have answered 2 of the 4 categories of data: attendance rates and grade. Lightning round! Is there a 5th category? In a sense, yes, because we have also filtered to see middle schools only. So we are really answering what our middle school attendance rates by grade, race and ethnic group are. So how do we add in the next category: race? One way would be to add another group for race, but if you guessed we are going to “pivot” on race, you are on the right track.

Adding pivot columns

Pivot columns are those columns which appear in the Column Labels section of the tool panel. So we want to get the race column in there. In the tool panel search box, type “race” and you’ll see something like this:

Using the mouse, grab the Race column label in the tool panel and drag it into the Column Labels box at the bottom of the tool panel. Assuming you have some variety in your race makeup, you’ll something like this:

Is that your first pivot table, ever? Party time…

Now that you have it, let’s talk about what we see. The column labels, which before was just a single “Attendance Rate (%)” has been broken down into one column for each race category in our data and labeled with that race data. So now we have “Asian Attendance Rate (%)”, “Black Attendance Rate (%)” and so on. This means we have answered for a 4th category of our (now) 5 categories of data question. The fancy word for this display of data is disaggregation or more simply put, we are looking at data by subgroups. The magic of Analytics is it formulates these subgroup breakdowns and calculations on command in a microsecond. Doing this manually would otherwise take hours, even days, when you consider the time it takes to manually collect, match, scrub, organize, calculate, correct and calculate again.

A pivot in a pivot: nested pivots

Just when you thought it was safe to claim victory, there is one last category of data to solve for in our question: ethnic group. Back to the tool panel, start typing “ethnicity” in the search box, you’ll see this:

We are going to pivot on Ethnicity and Race. Since Race is already in the list of Column Labels, just drag the Ethnicity column down to that section of the tool panel and drop it on top of the Race column. Drag the columns around so Ethnicity shows up first followed by Race, like so:

With that, you should see your data view transformed into something like this:

You now see Ethnic group categories (“Hispanic” and “Not Hispanic”) and their aggregations by grade, but what happened to the Race? The chevrons in the column headers next to the “Hispanic” and “Not Hispanic” tell you there is more to see. Click them, and you’ll see something like this: