2 Replies Latest reply on Apr 6, 2018 8:40 AM by Kurt Heisler

    Calculated field based on data that is filtered out

    Kurt Heisler

      I have data like below:

      Each row shows the school’s performance for a given year and cohort. The “Overall” numerator and denominator are the sums of the schools’ numerators and denominators for the given year and cohort. The “Overall” performance is the sum of the schools' numerators / the sum of the schools' denominator.


      I used these data to create this viz, filtered on school (“Anne” & “Overall”) and three of the cohorts:

      Rather than show the Overall performance as shown in the data, I would like to select (i.e., filter) a school (e.g., Anne) and have an “Overall” calculated dynamically based on data for the remaining schools.


      For example, if I filter to show only “Anne,” I would like an “Overall” performance calculated that is the sum of the excluded schools’ numerators (Smith and Jones) / the sum of the excluded school’s denominators.


      I assume the solution involves a calculated field, but I’m stumped how to handle it: When I filter on only “Anne,” data for the other schools are immediately unavailable and obviously can't be included in the calculation.


      My real data includes about 20+ schools. I create and send a school-specific dashboard to each school, so when I select their school I need the Overall performance to be updated accordingly.


      Any suggestions?

        • 1. Re: Calculated field based on data that is filtered out
          Zhouyi Zhang

          Hi, Kurt


          Please find my solution attached as well as below screenshot for reference.



          Hope this helps



          • 2. Re: Calculated field based on data that is filtered out
            Kurt Heisler

            That worked great!


            I had to multiply the unSelectedSchools calculation by 100 to put it on the same scale (e.g., 57.6 instead of .0576) as the hard coded Performance in the data. Otherwise, it returned .576 (e.g., Jul - Jun value) which it saw as less than 0 and put the line at the bottom of the axis. I tried changing various formats for the axes and pill but it wouldn't move. I'm not sure how you avoided the * 100 multiplier.


            My issue now is that I need to use the color legend with that graph (and about 15 other graphs for different metrics), but the blue box in the color legend is labeled "Selected Schools" whereas I'd like it to say the name of the actual school, in this case "Anne." (I renamed "unSelected Schools" to "Overall" so that piece is fine.) I read that dynamic legends aren't possible.


            I realize I can put a floating worksheet with the selected school's name next to the blue box in the legend (i.e., on top of the "Selected Schools" label)? Is there a more dynamic, scalable solution? Otherwise, I have to add and position a floating worksheet over the legend for 15 other charts where I'm using this technique.