5 Replies Latest reply on May 7, 2013 6:40 AM by Joe Mako

    Dynamic Bar Chart based on Drop Down Filters

    Ashish Agrawal

      I have attached a sample workbook and the raw data

       

      I have region-wise sales for current & last year & have made a bar chart for the same. Also, drop down filters have been added.

       

      I am looking that if in region, I select 'All', the chart shows me the sales chart based on Region wise (Europe/Asia), which means I wish to see only 2 bars.

       

      Similarly, if I select Europe, I should get bar charts for Germany & UK and if I select Germany, I should get bar charts for Frankfurt & Berlin.

       

      Please guide.

        • 1. Re: Dynamic Bar Chart based on Drop Down Filters
          Tracy Rodgers

          Hi Ashish,

           

          This can be done by creating a string parameter with a list of Region, Country and City. Then, create a calculated field using the parameter similar to the following:

           

          case [Choose Type]

          when "Region" then Region

          when "Country" then Country

          when "City" then City

          end

           

          Right click on the parameter and select Show Parameter Control.

           

          Hope this helps!

           

          -Tracy

          • 2. Re: Dynamic Bar Chart based on Drop Down Filters
            Ashish Agrawal

            Thanks Tracy.

             

            I probably missed to say that I need to have 3 dropdown filters. One each for the Geography level.

             

            The above solution works great in case of a single dropdown with all the geography values in it.

             

            -Ashish

            • 3. Re: Dynamic Bar Chart based on Drop Down Filters
              Joe Mako

              There are a number of ways to accomplish what I think you are asking for.

               

              One simple, but in my opinion incomplete, is to just use a calc field like:

               

              IF TOTAL(COUNTD([Country]))=1 THEN MIN([City])

              ELSEIF TOTAL(COUNTD([Region]))=1 THEN MIN([Country])

              ELSE MIN([Region]) END

               

              as you can see in the attached workbook on the Stacked bars sheet. What you will see that could be an issue is there is a mark, a bar segment for each City.

               

              If you want one mark for each Level-Year combination, for creating a line chart, then that requires some complexity with nested table calculations. You can see an example of this in the attached as well.

               

              If this is not what you are looking for, can you please mock up an image of each possible configuration you would like.

              1 of 1 people found this helpful
              • 4. Re: Dynamic Bar Chart based on Drop Down Filters
                Ashish Agrawal

                Thanks a lot Joe. Your approach has been really helpful, though I wanted the labels to aggregate at Year level in the bar chart itself. However, I am trying to convince the client with the same.

                 

                Also, I wish to replicate a table similarly. If I select Region, I get rows for the countries, and if I select a country, I get rows for city. I somehow tried with the above approach, but then for a region, I get all the different entries of all cities in the same row. I wish I could aggregate it.

                • 5. Re: Dynamic Bar Chart based on Drop Down Filters
                  Joe Mako

                  If this is not what you are looking for, can you please mock up an image of each possible configuration you would like.

                   

                  Your description is not clear to me, please provide an image of what you want for a result.

                   

                  My first thought is you want the complex route used to make the line chart but as a bar chart, if so, then you just need to change the mark type.

                   

                  The complex route uses nested table calculations, and has configuration that may not be intuitive. If you can post a packaged workbook of what you are getting, I will be able to see what configuration you are missing.