5 Replies Latest reply on Dec 6, 2013 8:52 AM by cory.tak.0

# How to place a subset of data and the entire data set on the same graph?

Hi All -

I am looking to create a single bar graph, with two columns. One column would be a total of all the products within a business line. The other column would be just a few of the products from that same business line. I'll use football as an example...

Column A of Graph X

Dimension: NFL Teams (All 32 NFL teams)

Measure: Total Points Scored

Column B of Graph X

Dimension: NFL Teams (Filter on just the Patriots, Giants and Eagles)

Measure: Total Points Scored

I can easily do this on two separate worksheets, but it is proving to be a challenge to do this on the same sheet.

Thanks!

• ###### 1. Re: How to place a subset of data and the entire data set on the same graph?

Hi Andrew,

I actually asked a very similar question a little while ago and got a very good answer:

Show aggregate data while using user filter

It was about using user filters, but it applies to filtering in general. The three options listed are

1. Duplicate your data source and blend in the unfiltered aggregated data (this is actually a lot easier than it sounds)
2. Use pass through functions to a DB (if you're in oracle you can use an analytical function to pre-aggregate the data) To use your example it would look something like this SUM(Total_points) over ( ) as total_points_scored (caution about pre-aggregating data before bringing it into Tableau. Tableau is designed to take the fine grain detail and perform the aggregation. If the data set is misused and the aggregate field ends up being 1,000,000 points someone might erroneously sum those data points together and conclude there were 32,000,000 points scored in the NFL last year)
3. Use table calculations (still works because of the order in which tableau carries out its operations: Business AnalyticsThe order in which Tableau carries out operations and calculations

I hope this is helpful. I know Robin Kennedy really helped me out.

Cory

• ###### 2. Re: How to place a subset of data and the entire data set on the same graph?

Hi Andrew,

I propose you this very simple solution to your scenario.

Create a calculated field based on Points Scored measure counting just the Points Scored by the subset of NFL Team using the formula:

```IIF([NFLTeams]=='Giants' Or [NFLTeams]=='Patriots' Or [NFLTeams]=='Eagles', [Points Scored], 0)
```

Then create a bar chart by dragging Measures Names to Columns shelf and Measures Values to Rows shelf

Filter Measure Names to keep the two measures of your interest [Points Scored] and the new calculated field Points Scored by Giant Patriots Eagle

I created a sample data set and a workbook to demonstrate this solution.

I hope this helps

best,

Ramon

• ###### 3. Re: How to place a subset of data and the entire data set on the same graph?

Ramon - unfortunately your calculation will not work because my "total points" measure is aggregated in the data source. So I get an error with your formula that I can't mix aggregate and non-aggregate. Any work arounds?

Cory - I am having trouble following your response. I will try to wrap my head around it and see if it works.

thanks!

• ###### 4. Re: How to place a subset of data and the entire data set on the same graph?

Hi Andrew,

Best,

Ramon

• ###### 5. Re: Re: How to place a subset of data and the entire data set on the same graph?

Hey Andrew,

Here's an example of duplicating your data source for the desired result.