1 Reply Latest reply on Nov 29, 2015 10:04 AM by Matt Lutton

    How can I make my window_median calculation take into account my filter?

    Nick Shatz

      I cannot get my window median to work correctly. In the attached workbook see that "%v median" and "Total median" are incorrect with values of 43.0% (should be 37.3%) and 14,456 (should be 3,771,565) respectively. The 43% and 14,456 reflect the median for the underlying data when the 1% filter is not applied. I would like for the median to take into account the 1% filter that I have applied in the workbook.

       

      Ultimately I wan to make one  sheet "Unmet Potential" which will be filtered to display only types that meet the unmet potential logic (included in the capacity bottleneck & unmet potential calculated field) and another sheet "Capacity Bottleneck" that displays only types that meet the capacity bottleneck logic.



        • 1. Re: How can I make my window_median calculation take into account my filter?
          Matt Lutton

          Let me know if this gets you exactly what you need or not -- Table Calculations happen very late in Tableau's order of operations/pipeline, so the Filter you had created impacted the Table Calc since the Table Calc happened after the filter.

           

          By using a FIXED LOD expression to create your filter, I believe we can resolve your issue -- take a look at the new formula for the "1% filter" and let me know if this gives you the result you wanted.

           

          More info on why this works here, from Tableau's own Bora Beran:   What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions « Bora Beran

           

          LOD Solve.png

           

          There were a few other measures and filters in place, so I just want to be sure the above is your expected output.  Let me know if you have any questions.  The FIXED LOD expression allows us to create the filter without impacting the Table Calc results (FIXED essentially means that your former WINDOW_SUM measure can now be FIXED to the total across the entire data set, and this FIXED expression happens before Dimension and Measure filters in Tableau Desktop, so the filter doesn't impact the percentage column you were concerned with) -- Study Bora's blog post on LOD for further understanding -- its a great resource!

           

          The filter diagram on his blog is especially helpful:

           

          Filter Diagram.png