6 Replies Latest reply on Jun 24, 2013 12:57 PM by . Rmi03

# Calculate % Point Difference from two worksheets, same data source

I have two worksheets on a dashboard.  One worksheet is for a city and the other is for the state.  Each worksheet is looking at the same information.  Distribution of the population by age.  As an example, the city % distribution of 50+ year olds is 40%.  For the state, it's only 20%.  The city is 20 ppt higher.

My question is can you create a calculated field to show the 20 ppt difference on one of the two worksheets are on a third worksheet?

I tried to build one worksheet with both city and state but was unsuccessful.  My requirement is that I have several cities with different states that I need to be able to do this for and to view the city/state one at a time on the dashboard. Using a city and state filter is what I am thinking.

thanks...

• ###### 1. Re: Calculate % Point Difference from two worksheets, same data source

This can be done. If you post a packaged (twbx) workbook with one state and one city, set up the way you want it, we can show you how to do the calculation.

--Shawn

• ###### 2. Re: Calculate % Point Difference from two worksheets, same data source

thanks Shawn,  I'll put one together Monday.

• ###### 3. Re: Re: Calculate % Point Difference from two worksheets, same data source

Is something like this what you wanted?

• ###### 4. Re: Re: Re: Calculate % Point Difference from two worksheets, same data source

Thanks for the workbook.  After reading my question, I realized that I left out an important part of my example.  I took your info and built a three state region with 50+ year old population by city.  Los Angeles has a 24.39% pop of 50+ Age and the State has 39.81%, a ppt difference of

-15.42%.

I would like to be able to see three columns with the above info side-by-side on one worksheet and I would like to be able to filter by city.

Updated:  I know how to build the city and state on two separate worksheets and use action filters on a dashboard to fitler each one.  But, I have to manually calculate the ppt difference between the city and state.  It would be nice to have it all on one worksheet.

thanks,  Ron

• ###### 5. Re: Re: Re: Calculate % Point Difference from two worksheets, same data source

Well I think you will need to include the total populations of the city in order for the data to make sense. That way you should get an actual average of the total populations per state and per city. Right how the data is, all you really see is that CA has 40% of the 50+ year old's of the 3 state group, and not a population made up of 40% 50+ year old's.

• ###### 6. Re: Re: Re: Calculate % Point Difference from two worksheets, same data source

I agree, since we are playing with dummy data, I didn't think of building the other ages.  But you are correct about the data being misleading.  Given the data I have, do you see a way to show all three measurements of 50+ Age (city, state, ppt difference) on the same worksheet with a city filter?

If it will help, I can rebuild it this afternoon and show four age ranges to cover the entire population by city.

Thanks again,  Ron