# How to visualize this data in desired format?

• ### Question

• I have a table with four columns: (STATE, COUNTY, CITY, POPULATION)

State: Name of states (S1, S2....)

County: Name of Counties (C1, C2...)

City: Name of Cities (CTY1, CTY2...)

Population: Population in that city.

Requirement: When I select any row in table (Say row2), I need to create a pivot barchart showing corresponding populations of that city (CTY1), of the entire county(C1) and entire State (S1).

Example: If I select row 2, I need a bar graph for population values for CTY1, entire C1 (i.e. sum of D2:D7), and entire S1 (i.e. Sum of D2:D18)

How can i do that?

Here is the link for the sample sheet: https://skydrive.live.com/redir?resid=D4CF3C81B41F8F46!147&authkey=!AEIsKAYaGcTRAec

Vishal Soni

Monday, April 15, 2013 4:43 AM

• Not totally sure this is a PowerPivot question but you could certainly use it to solve the problem, although not necessary exactly as you describe.

The idea is a Pivot Chart with a slicer on City where you write measures that sum the population for the city, county and state respectively. e.g.

`[City Population] = sum(Table1[Population])[County Population] = CALCULATE([City Population],FILTER(ALL(Table1),Table1[County]=values(Table1[County])))[State Population] = CALCULATE([City Population],FILTER(ALL(Table1),Table1[State]=values(Table1[State])))`

This mean you can create a workbook like this:

I uploaded the workbook on SkyDrive if it's useful.

Jacob

• Proposed as answer by Wednesday, April 17, 2013 6:10 AM
• Marked as answer by Wednesday, April 24, 2013 9:59 AM
Monday, April 15, 2013 6:22 AM

### All replies

• Not totally sure this is a PowerPivot question but you could certainly use it to solve the problem, although not necessary exactly as you describe.

The idea is a Pivot Chart with a slicer on City where you write measures that sum the population for the city, county and state respectively. e.g.

`[City Population] = sum(Table1[Population])[County Population] = CALCULATE([City Population],FILTER(ALL(Table1),Table1[County]=values(Table1[County])))[State Population] = CALCULATE([City Population],FILTER(ALL(Table1),Table1[State]=values(Table1[State])))`

This mean you can create a workbook like this:

I uploaded the workbook on SkyDrive if it's useful.

Jacob

• Proposed as answer by Wednesday, April 17, 2013 6:10 AM
• Marked as answer by Wednesday, April 24, 2013 9:59 AM
Monday, April 15, 2013 6:22 AM
• Hey Jacob.

Thanks for putting this up, but somehow, i am not able to test this here. In my system i have office 2013, and the sheet says that the data model needs to be updated before i can view it. I would try using the formulas you posted.

Thanks.

Vishal Soni

Wednesday, April 17, 2013 4:33 AM
• If you press OK and follow the steps then it will update the model to a 2013 version.

Jacob

Wednesday, April 17, 2013 5:23 AM
• Hey Jacob,

In this excel, i get the error, "data model needs to be updated before i can view it", and when i click on 'OK' in the message box, it just disappears, but no instructions/steps after that. when i click on the excel again, it again displays the same error. Its not upgrading the data model automatically.

Vishal Soni

Tuesday, April 30, 2013 6:37 AM
• I tried to do the update and I also got the error! Fundamentally the measures are the only thing of importance!