locked
How to visualize this data in desired format? RRS feed

  • 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

Answers

  • 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 Jacob Barnett Wednesday, April 17, 2013 6:10 AM
    • Marked as answer by Elvis Long 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 Jacob Barnett Wednesday, April 17, 2013 6:10 AM
    • Marked as answer by Elvis Long 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!

    Jacob | Please mark helpful posts and answers

    Tuesday, April 30, 2013 9:50 AM