7 Replies Latest reply on Dec 4, 2018 1:24 PM by patty.yang

    Exclude value based on Parameter selection

    patty.yang

      Hi all,

       

      I have a parameter called Metro Areas, with values Austin, Houston, Dallas, and All. In my worksheet, I want to create a calculated field that that exclude value based on the parameter selected but show everything else.

      For example. if parameter select Dallas, I need the measure to show count of people for all but Excluding Dallas.

      How can I achieve this?

       

      Thank you all for your help!

        • 1. Re: Exclude value based on Parameter selection
          Ombir Rathee

          Please post a sample workbook.

          • 2. Re: Exclude value based on Parameter selection
            Jen Shepherd

            Hi Patty,

             

            Without knowing your field names, your data structure, and what else is going on in your viz, it is hard to give you "the" solution.  As Ombir suggested, a sample workbook would be really helpful.  That said, something like this calculation might work for you:

             

            sum(if [City] = [Metro Areas] then NULL else [Population] end)

             

            where Metro Areas is your parameter, City is the dimension in your data set for which you're calculating population, and Population is the measure.  Translated: If the data record is for the same city as that selected in the parameter, treat its population as null, then sum up the population value from all the records.

             

            You'll need to make sure your parameter values exactly match the formatting of your City field (capitalization, etc.) or adjust the calc to make it match (upper, lower, trim, etc.). 

             

            This calc doesn't address the "all" option you mentioned because it didn't make sense to me why you'd want your users to exclude all cities if you're showing a population total.  Unless you meant for "all" to mean all three of the city options in your parameter (Houston, Austin, and Dallas) rather than all cities in your data? 

             

            Another way of approaching this would be to add to your viz a quick filter for City with the Exclude Values option enabled so users can eliminate the cities they want.   No parameter needed! 

             

            If you still need assistance, please explain what you want to happen for the "all" option, and if possible, provide a sample workbook (sometimes SuperStore is a decent proxy for your real data) or at least screen shots.  In particular, we need to see what you have on your rows, columns, and marks shelves and understand any other calcs in play for your viz.


            Best,

            Jen

            • 3. Re: Exclude value based on Parameter selection
              patty.yang

              Hi Jen,

               

              Thanks so much for taking the time to explain. My 'City' field is actually not City, it is actually made up by Counties. I grouped the counties into 5 major metro areas and the rest are 'Other'.

              I would like to show a comparison between two different measures, one, using the parameter as filter, showing the count by the metro areas. two, using the same parameter showing all records, but excluding the metro area count when selected.

              is this even possible?

               

              i have attached a sample workbook.

               

              Thanks so much!

              • 4. Re: Exclude value based on Parameter selection
                Ombir Rathee

                I don't understand your requirement. Could you please show your desired output with the help of numbers ?

                • 6. Re: Exclude value based on Parameter selection
                  patty.yang

                  Thanks Ombir, unfortunately its not right.

                  if you look at 2018 for example, the total is 50575, I need the calculation to show 48509 (50575-2066) if the metro parameter is selected on Rio Grande.

                   

                  hope this helps!

                  Thank you again

                  • 7. Re: Exclude value based on Parameter selection
                    Jen Shepherd

                    Hi Patty,

                     

                    Here are a couple of options, using the same calc idea I provided earlier:

                     

                    Data table:

                     

                     

                     

                    With none excluded:

                     

                    Part to whole bar graph:

                     

                     

                    I've attached the workbook as well, v 2018.2.3.  Hopefully this will give you some ideas, otherwise the more clear you can be about how you want it to look and function, the better we'll be able to help. 

                     

                    Jen

                    • 8. Re: Exclude value based on Parameter selection
                      patty.yang

                      Thanks so much Jen! This has helped a lot!!!!!

                      REALLY APPRECIATE IT!

                       

                      Patty