3 Replies Latest reply on May 28, 2012 5:47 PM by Dimitri.B

    Using multiple measures with different filters

    Robyn Stables

      Hi there,

      I have data for test results for students at my school.

      1.  The sample set I am using is for one subject (however there will be mulitple subjects - but it I get this to work on one, I am away).

      2.  There are 4 results per child - for Year 7, Year 8, Year 9, Year 10.

      3.  I wish to see a graph for one child (hence a filter on child name), showing their results across the 4 years.  This I can achieve.

      4.  I wish to also see, on the same graph, in a different colour, the average results for all children for that subject.  So that we can see how the child sits against all other students.

      5.  My problem is that when I use multiple measures in the one graph, I can't seem to apply different filters to each Measure.  For example I would like the average Measure to never change and always be the average of all children for that subject.  However I would like to be able to switch between difference students for the other measure when ever we need to look at a particular child.

       

      I have attached my little file, I hope this makes sense.  I am in the early days of learning the software, so I know I am missing something obvious.

      I tried to create an average Measure with a formula - but I think this might be where I am going wrong.

        • 1. Re: Using multiple measures with different filters
          Dimitri.B

          The challenge here is to filter data to one child, but at the same time show average calculated from all children.

          These are contradictory requirements, but there is a workaround using parameters. It is not ideal and may not work well if the list of students is constantly changing, but it is an option.

           

          The steps are:

          1. Create parameter that will select a child. No need to enter each name - just select 'List' and 'Add from field' button (see 'Selected Child' in the attached example). Note that this will not be automatically updated if the data changes. Show parameter control (right-click).
          2. Create a calculated field that will determine for each child if he/she was selected via parameter ('Is selected child' in the example).
          3. Create a calculated field using table calculation formula to calculate average for all ('Listening Avg' in the example). This will later need to be properly partitioned using 'Advanced' option.
          4. Build a view like shown in the example ('Listening fix stage'), putting 'Listening Avg' on dual axis and partitioning as shown (to see, right-click -> 'Edit Table Calculation...' )
          5. Right-click - Hide 'False' section of the view to only leave selected student visible, hide headers for 'Is selected child' and 'Student' (right-click -> untick 'Show Header')
          6. Add additional tweaks, like fixing the axis scale, adding title with parameter to show student's name, etc.

           

          This may not be a really simple solution, but I can't think of a simpler one.

          • 2. Re: Using multiple measures with different filters
            Robyn Stables

            Thank you for your reply, however I still seem to be having the same problem.  I need the average to not change, and only the Student line to change if the name is changed.  Even in your one when I select the Student name, both the Student and the Average line changes.  Maybe I am still doing something wrong.

            Robyn

            • 3. Re: Using multiple measures with different filters
              Dimitri.B

              I suspect that what you see is an illusion of changing avg line, because the graph automatically adjusts the scale of the axis. Edit axis and fix the scale to between 0 and 7 and try again - you'll see that the avg line remains fixed.