5 Replies Latest reply on Feb 28, 2017 2:39 PM by Joe Oppelt

    How to show selected respond while displaying overall min, max, avg marks

    Xi Chen

      Hi,

      I'm seeking for solutions to help me implement the following needs of displaying data:

      1. I was able to build a parameter as "select company", now I only want to see the selected company's data- as a circle or any other shapes. Do not display other responds, I was thinking to set their color to transparent but I don't know how.

      2. At the same time, I want to add the Max, Min and Average marks in the same graph so I can compare the selected data with overall company data.

      3. When I use the parameter or filter to switch between companies, the Max, Min and Average marks won't change since they are refer to overall data.

       

      I attached one template workbook so you can try to work on it. But the actual workbook I'm working on is huge and much more complicated so I'm also looking for a simple way to implement my needs. It's better not to create too many calculation fields since I will have almost 100 objects that need to be built in charts.

        • 1. Re: How to show selected respond while displaying overall min, max, avg marks
          Joe Oppelt

          (Note to self:  V 10.0 workbook here.)

           

          See attached.

           

          When you use a table calc as a filter, you don't lose the underlying table.

           

          In sheet 1 I have your data displayed.  And in the title I have an average of all the data.

           

          In sheet 2 I used LOOKUP to select one company, controlled by a parameter.  LOOKUP is a table calc. You can see that even though one company is displayed, the overall average value in the title remains intact.  This lookup assigns a value of 1 or 0 depending on the company, and the actual filter values would be either 1 or 0, and I select for 1.  I don't display the filter.  I display the parameter for the user to select from.

           

          In sheet 3 I used a different LOOKUP to allow for multiple selections.  This LOOKUP becomes the filter itself, and I have displayed it as a filter.  (You could actually set this up as a single value filter, effectively acting exactly like the parameter in Sheet 2.)

          • 2. Re: How to show selected respond while displaying overall min, max, avg marks
            Xi Chen

            Hi Joe,

             

            It is interesting to see you added average revenue with company revenue in one sheet.

            But actually what I'm looking for is the visualization of the company revenue, average revenue, the maximum revenue of all companies and the minimum revenue of all companies in one chart.

            It could be using a circle or bar to represent company revenue, and markers to represent MAX, MIN & AVG.

             

            Is there any way to implement it? Thanks a lot!

            • 3. Re: How to show selected respond while displaying overall min, max, avg marks
              Joe Oppelt

              You'll make another calc to do MIN revenue just like I did AVG.  Likewise MAX.


              And then it's just a matter of creativity to display what you need -- whether it's all on one sheet, or on separate sheets.  I can't picture what you are really shooting for, so I took a stab at it in Sheet 4 of the attached.  Really the actual visualization is a matter of what your users want to see, not what I think they want to see.  And it should be left as an exercise for you to shape your visualization as you see fit.  I made a MIN and a MAX calc, and then I used dual-axis to overlay the table calcs on a bar.


              I just gave you the tools you need (specifically using a table calc as a filter) to give you the necessary access to all your data while limiting what gets displayed.

              1 of 1 people found this helpful
              • 4. Re: How to show selected respond while displaying overall min, max, avg marks
                Xi Chen

                Hi Joe,

                 

                The sheet 4 is exactly what I need! It's really helpful.

                Now I only have one small question about the visualization. If I want to mark max & min in Gantt Bar, Ave into Plus shape and Your company into Circle, how can I implement. Now I can only figure out to dual Axis two groups not three groups.

                 

                Thanks,

                 

                Kiko

                • 5. Re: How to show selected respond while displaying overall min, max, avg marks
                  Joe Oppelt

                  Dual axis will only let you crunch two chunks of stuff together.  And you can specify one type of formatting for each chunk.  (So I did bar with one axis, and shapes with the MeasureNames axis.)

                   

                   

                  See Sheet 5 of the attached.  I moved the MAX and MIN from the Measure Names pill into reference lines on the sum(Revenue) axis.  That leaves the AVG calc as its own axis.

                   

                  Because there are table calcs involved, I can't get the two axes to synchronize, so I also put the MAX as a reference line on the AVG axis.  (I formatted it so that there is no actual line and no actual label, but it's still on there.)  So each axis has the same max value and they line up because of that.  It's a hack way to force axes to align.

                   

                  So it's not exactly a Gantt, but you effectively have the same sort of visualization.  Now the sum for the company is a circle.  (You can mess with size and color, etc.)  And the AVG is a plus.

                  1 of 1 people found this helpful