6 Replies Latest reply on Apr 12, 2013 2:41 PM by Michelle Lund

    How do I create a measure that groups data based on area?

    Michelle Lund

      I have been trying without success to create a measure that flags data that I have selected from a map and gives another code for data that I didn't select.  What I really want is a measure that assigns a code of "1' to data that I have selected from a map and then a code of '2' to data that I have not selected. 

       

      I tried to use a 'Set' to do this, but it didn't do what I wanted - what I did was to highlight the datapoints of interest on my map, selected 'Exclude'  and then went to my filter and right clicked on the filter that excluded these wells and hit "Create Set'.  However, if I tried to use this new Set to color datapoints on a different graph, all the data not included in the set would disappear and each datapoint included in the Set would get a different color based on its unique identifier.  What I want is to color all data included in the selected set ONE color  and data  not included in the dataset another color.  So I keep thinking that if I could create a calculated field that assigns a value of "1" if the data is in the Set and "2" if it is not, that would take care of the problem, but I can't see a way to do that.

       

      I am out of ideas - HELP!!

        • 1. Re: How do I create a measure that groups data based on area?
          Richard Leeke

          What you want is an ad-hoc group. This allows you to group the points of interest in the way you want.

           

          Selecting the points spatially off a map and generating the group that you want is a little bit convoluted (I think - others may point out an easier way - as always I'm sure there are lots of ways to do this).

           

          It's easiest to explain with an example - so I've attached a Superstore Sales workbook.

           

          1) On sheet1 I've selected a few clusters of customers (hold down ctrl while you select) and created a set called Selected.

           

          Selected.png

           

          2) On sheet2 I've added the Selected set as a filter and also put Customer Zip Code on the rows shelf.

           

          Select all zip codes, and then hit the group (paperclip) icon on the toolbar:

           

          Group.PNG

           

          3) That will create a group containing all the members of the Selected set (and replace the Customer Zip Code dimension on rows, with the newly vreated group):

           

          Edit Group.PNG

           

          Right click on the group name in the data window (highlighted) and choose edit. That will display the current gorup members, which will be an individual entry for each of the non-selected rows plus one entry for the group of all selected ones.

           

          Scroll down the list until you find the entry for the selected group, right-click it and rename it to "Selected".

           

          Then select all the other entries and hit the Group button. Name this lot "Not Selected".

           

          4) Finally, create a new sheet, put customer Zip Code on Level of Detail and Customer Zip Code (group) on the color shelf.

           

          Finished Map.png

           

          Can anyone think of a shorter route? It feels to me as if there probably is one.

          1 of 1 people found this helpful
          • 2. Re: How do I create a measure that groups data based on area?
            Michelle Lund

            It took me this long to get back to this because I had moved onto other things, but I ran into the same problem again, and your solution worked like a charm!  It certainly is not something that I EVER would have found on my own so it is great to have this forum to turn to when I get completely stumped. 

             

            Thanks!

            • 3. Re: How do I create a measure that groups data based on area?
              Michelle Lund

              Ok, now I am trying to take things further by selecting a number of separate areas on a map and assigning a different code to each area selected so that I can color them differently on other graphs.  The above methodology works great when I only have 2 areas (ie 'selected' and 'non selected') but I can't use it if I have to work with multiple regions.  Any ideas?

              • 4. Re: How do I create a measure that groups data based on area?
                Richard Leeke

                The group approach should work fine with multiple groups, but I do recall that it can be a bit tricky to select multiple groups like that by selection from the map. It can be done though. I'll dig out the example workbook and put together a few more screenshots leading you through the process when I get a moment...

                • 5. Re: How do I create a measure that groups data based on area?
                  Richard Leeke

                  No time for screenshots, but here's a brief description of what you have to do plus an extended version of the example I posted before.

                   

                  Basically you follow the same process as before, with a few modifications.

                   

                  In step 1, you need to select each collection of marks that you want in turn, creating several sets.

                   

                  Then repeat steps 2 and 3 for each set in turn (i.e. replacing the set on the filters shelf with each set in turn, repeatedly hitting the group paper-clip.  Note that you mustn't create the final group of all the remaining ones until the very end.

                   

                  Note that I had to create an extract because I got a "query too complex" error from the "Jet engine" which handles Excel files.

                   

                  Several groups.png

                   

                  Message was edited by: Richard Leeke Replaced image which the forums had lost.

                  • 6. Re: How do I create a measure that groups data based on area?
                    Michelle Lund

                    You know, I always come back to this post because I keep forgetting how to do this - thanks a lot for your answer.  Really, we should be able to make this more intuitive in the future.  I can't be the only one who keeps needing to do this....