4 Replies Latest reply on Nov 27, 2016 6:18 PM by Ryan Smith

# Show Subtotals Only

I have a data set with about 40,000 records (10,000 people) and various quarters. Here is an example in Excel. The Totals are aggregate numbers calculated in Tableau (average):

Because of the nature of the data, all I want to show is quarterly averages. I don't care about the individual IDs. So, I would like a table like below. My goal is to then conduct analyses based on ID demographics, like division or scores on employee competency exams.

This is relatively easy in Excel using Pivot Tables, but I can't figure it out in Tableau. I tried the First()=0 table calc, but it only works for the first row in the entire table. In fact, it highlights the first row, not the subtotal row (so first()=0 shows the \$5 for ID 1, not the \$6 subtotal average). I only want the first row in each quarter. When I hide or get rid of the ID info, it effects the Total (or average) row. Is there a simple way to either 1) just show the subtotals (or in this case, average) or 2) to create a table that just shows the averages from scratch? Thank you.

• ###### 1. Re: Show Subtotals Only

Pull in the data without the subtotal, add the Quarter field to the row shelf and drag in Salary and set the aggregation to AVG.

1 of 1 people found this helpful
• ###### 2. Re: Show Subtotals Only

This is pretty straight forward in Tableau. Just use Quarter of the date you have. And then drag the measure to the text shelf and set as AVG(). See attached.

1 of 1 people found this helpful
• ###### 3. Re: Show Subtotals Only

If ''Total" is already a in the ID column of the spreadsheet you are using as a data source (this isn't ideal but) you can probably filter the data source where ID = "Total". Then you can observer the Quarter / Total average subtotal you want.

To do that right click on the data source and select edit data source filter. Then add ID = 'Total'.

• ###### 4. Re: Show Subtotals Only

That worked. What a huge relief. Thank you for your prompt reply and have a good week!