5 Replies Latest reply on Mar 28, 2018 12:53 PM by Joe Oppelt

    Comparisons across filtered dimensions

    Daniel Meaney

      Sorry, I'm fairly new ...


      I have population and demographic measures for thousands of city blocks (census blocks) in a county.  Each is coded to its city (59 cities) and a status code (food desert = Y or Null)


      I've created a series of pie and bar charts that are filtered to show aggregated data for one city at a time for each of several measures (population, poverty, etc.).  For each of those measures, I'd like to compare the data for that one filtered city against a total for all cities.


      In my eventual dashboard, citizens would pick their own city and see the comparison against the sum of all cities.


      Will this require separate charts for the 'all cities' totals, then arrange them side by side on the dashboard? Or is there a way to work with filters to manage this?


      See worksheets: PiePop, PieMinorityPop, HealthBar Filter

        • 1. Re: Comparisons across filtered dimensions
          Joe Oppelt

          If you make this calc:


          { FIXED : SUM[Eblk TotMI]) }


          It will give you a sum across the whole data source for that measure.  No matter what filtering you do on your sheet, that total number will be available to you.  (And because it's a FIXED LOD, you can make that be either a measure or a dimension.)  In essence you get that total value on every row in the data source.


          If you want the sum across the data source broken out by Food Status, change it to be this:


          { FIXED [Food Dessert Status] : SUM[Eblk TotMI]) }

          • 2. Re: Comparisons across filtered dimensions
            Daniel Meaney

            Thanks.  Looks like that would get me the County total measure(s), but then how can I manage the filtering so that I can always incorporate a user-selected area AND the County as a whole. Seems like I would

            force the user to make multiple filter choices (a focus city and the County) in order to see the comparisons.  In any case, I'd like to end up with side-by-side pie charts and/or grouped bar charts to compare the two areas.

            • 3. Re: Comparisons across filtered dimensions
              Joe Oppelt

              You're immersed in the details of your workbook and I'm not.

              Where does County come into play.  I don't see a field for that.


              On Sheet PiePop I added two calcs.  One is the total throughout the data source for SUM(Population), and the other is the sum(population) on the sheet divided by the overall total population.  See Calc1 and Calc2.

              • 4. Re: Comparisons across filtered dimensions
                Daniel Meaney

                I see your calculations, but not clear on how to incorporate them into my charts. 


                For instance, I'd like to make some side-by-side pie charts with one showing a selected area (from the filter value), and a second pie for the sum of all areas (County total).  Can I do that in one 'worksheet' or Is that going to have to be separate worksheets combined on the dashboard?  Seems like I could always do that if I don't get the more sophisticated method.

                • 5. Re: Comparisons across filtered dimensions
                  Joe Oppelt

                  If you want to have one pie chart for a single dimension value, and another for the sum of all values, then you'll want to do them on separate sheets and position them side-by-side on a dashboard.