1 Reply Latest reply on Nov 14, 2018 8:36 PM by swaroop.gantela

    Help Using LOD to Benchmark - Multiple Filters

    Samantha Richardson

      Hi, Tableau Community -


      Would love any help as I'm trying to use a LOD expression to benchmark a select group of schools' overall performance compared to overall performance for all schools in the dataset. My dataset has school-level data for 6 key metrics, for each of which all schools' data roll up into a program-wide average. The workbook I'm using has sensitive data so I can't share it.


      In my view, I want to display performance for a subset of schools against a dynamic reference line showing overall average.


      Right now, I'm using a fixed LOD, which I don't think is the right LOD. The LOD is performing as expected based on definition- in that the reference line remains fixed regardless of the filter selection. But, I actually need the reference line to change based on the filtered selections.


      Here are 3 views from my worksheet to help illustrate what I'm trying to achieve. First, View A, where no filters are selected (view behaves as I wish) -  the reference line really isn't needed with no filters applied as the reference line value = the bar chart value.

      View A.png


      View B) When the filter "Boston" is applied to Program - the chart behaves as desired, with the blue bar changing to indicate the average for schools that fall into the Boston program and the reference line still indicating all schools.

      View B.png

      View C: now, when the filter "Boston" is applied to Program AND a the filter "lower Range 0-7%" is applied to Range: Senior Class Size, the chart does not behave as desired. While the blue bars move as they should, the reference lines remain in the same place as they do in View B. I want the reference lines to update as well to now reflect schools in the larger, overall dataset that have senior class sizes in the "lower range 0-7%" bucket from the "Range: senior Class Size" filter.

      View C.png


      The LOD expression I'm using is { FIXED [KPI1]: AVG([2018-19 School Performance])}, where KPI1=the six metrics on the horizontal axis, and 2018-19 School Performance is each school's performance.


      Any help on how to make my view behave as I want it to for View C would be so appreciated! I'm thinking I need to use include or exclude LOD expressions but can't figure this one out.