4 Replies Latest reply on Mar 29, 2018 1:34 PM by susheela.hegde

    How To Make multiple Parameters relevant like Filters

    susheela.hegde

      Hi All, I would like to have two parameters City and Region as attached in the worksheet.

      In sheet 1, If I select Region Parameter as East , City will display All .

      When I select any Market respective Region gets displayed.

      This is what I want But When I have clicked All for  the Parameters, I want All to be displayed for Both the fields and not by individual data(like central, east). Basically I want relevant values when I select either of them. If Both are All then display All for the fields. Is this doable?

      Also, In sheet Rank function Help, I would like to calculate Top cities with high   % of Profit(Percentage of total)  only for technology Category.

      Can anyone of you help me with both the calculations?

      TIA!

        • 1. Re: How To Make multiple Parameters relevant like Filters
          Jim Dehner

          Good afternoon

          see the attached possible solution to your ranking issue

           

          First to the Parameter issue you raised - don't think that will be possible - parameters are singled valued and static - an apply to the entire workbook (global) and to the point they remain constant until manually changed by the user - so you would have to change one and then the other

           

          now to the rank

           

          I used a formula like this

           

          and then created a viz

           

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: How To Make multiple Parameters relevant like Filters
            susheela.hegde

            Hi Jim, thank you for your reply. Issue 1: Is there any other way to display values All and relevant value in the pane ?Using filter? Issue 2: I would like to display all the categories for all the cities and then sort only by % of profit across cities descending. Is this doable?

            • 3. Re: How To Make multiple Parameters relevant like Filters
              Jim Dehner

              Yes you can delete the City parameter and replace it with a filter set to relevant values and leave in the region parameter

               

               

              you have raised a different question that wasn't part of the original post

              There is a table calculation that calculates % of total and you can sort the grand total on that value

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: How To Make multiple Parameters relevant like Filters
                susheela.hegde

                Thank you Jim. Sorry for confusing with the questions. I did not raise a different question.

                 

                Issue 1 was part of Parameter/filter. Currently I have parameter. If Parameter is static,then can we do by filter was my question. But just with filter, we cannot display the Value "All" when all the region is selected. Instead it will display each region and not All. Is there anyway we can display City and Region With "All" ?

                 

                Issue 2: I would like to display each category . and sort City by % of Profit only for Technology Category. so for Example,  % of profit is computed table across(across category) for all the cities. Now sort % of profit descending only for technology by city.

                1. show all the categories for all the cities.

                2. Calculate % of profit across each category for each city.

                3. sort % of profit for technology only and see how many cities are top with high % rates for technology out of all categories.