4 Replies Latest reply on Jun 11, 2013 12:54 PM by sira.sasitorn

# Summarizing at different granularity

Hi,

I'm trying to calculate measures based on the same formula but summarized at different levels. For example,

 State Sales Zip Value 1 Value 2 Rate (Value 2/ Value1) Statewide Rate IL Sales 1 60601 10 2 0.4 0.37 IL Sales 2 60601 15 8 0.4 0.37 IL Sales 3 60090 5 1 0.2 0.37

The bolded column headers are the ones I want to calculate. Rate and Statewide Rate are calculated using the same formula, which is [Value 1 / Value 2]. However, it is summed up at different value.

Rate is being summarized at Zip level. So (8+2) / (10+15). Leaving out the 3rd row since it is from another zip.

Statewide rate is being summarized at state level so it is going to sum up all Value 1 and Value 2. so (2+8+1) / (10+15+5).

The table above is just to explain the logic, I would like to build a line graph based on Rate and Statewide Rate (to compare). I tried using running_avg but the numbers are still wrong. Is there another way to approach this?

Thank you!

• ###### 1. Re: Summarizing at different granularity

Hi Sira,

You were close. You want WINDOW_SUM(SUM([Value1])) / WINDOW_SUM(SUM([Value2])).

The key is creating 2 fields with that as the calculation, then setting them to the appropriate different table calculation settings.

For the STATE RATE field:

Computes using: Advanced >> State, Zip and Sales (the order matters)

At the level: Sales

Restarting every: State

For the ZIP RATE field:

Computes using: Advanced >> State, Zip and Sales (the order matters)

At the level: Sales

Restarting every: ZIP

Does that help?

1 of 1 people found this helpful
• ###### 2. Re: Re: Summarizing at different granularity

You answered my question. I have an additional question. I'm trying to make a graph where I compare STATE RATE to ZIP RATE to see how the ZIP is doing compare to STATE, so I will be having ZIP as my global filter. In the picture below, the bar chart is Value 1, and the lines are STATE RATE and ZIP RATE. The X axis is the date field. If I have ZIP as a global filter, STATE RATE will be filtered and have the same value as ZIP RATE right? I couldn't find a way around this. Thanks again for your help.

• ###### 3. Re: Re: Summarizing at different granularity

Hi Sira,

Small world! Another forum post I was responding to was essentially after the same thing. "ZIP" is really a sub-classification of "STATE". You might find this thread helpful: http://community.tableau.com/message/214173?et=watches.email.thread#214173

The issue that complicates things here is that if you try to filter to a specific ZIP code, you are tossing out all of the records in that same state for the other ZIP codes, so I don't think you can get the STATE total you want--at least not with a single data source.

The simplest solution, I think is to use a 2nd (duplicate) data source to do your STATE level metric. You would link the two data sources on Date and build the table calculation the same way in the 2nd source.

Also, this link to the knowledge base describes the process: Showing Summary and Detail Together

• ###### 4. Re: Summarizing at different granularity

Hi Mark,

Thank you for your advice. I tried having 2 data sources. One at ZIP level, and the other at STATE level. Then I build the graph on the ZIP level data source and drag in Statewide Rate (that's computed on STATE level data source) field into the graph to compare (as in the picture above).

The problem is, The global filters in ZIP doesn't filter the Statewide Rate too. Eventhough the statewide rate is in the same worksheet. As a result, Zip rate will be correct, but the Statewide rate will be wrong since it will ignore the filter such as months and aggregate everything.

Is there a way to carry the filter over? I tried using parameters but it doesn't work since because both fields from different data source are in the same worksheet.

Any help is very much appreciated.

Thanks again!