6 Replies Latest reply on Sep 14, 2015 9:30 AM by Mitchell Gulbransen

    How can I calculate range penetration %?

    Mitchell Gulbransen

      I am looking to build a heat map with Month as the columns and several different measures as the rows, but I want the color scales to be exclusive to the range of each row - the first row may only have a range of 0-10 while the last row has a range of 50-200, and I don't want all the dark colors skewed towards the bottom.

       

      In order to accomplish this, I am trying to create a calculated field that shows each value's % penetration into the range of the given measure.

       

      For example, if the values for a particular measure are:

      Measure1: 0,1,3,3,7,8,9,10

      Measure2: 15,20,33,77,80,82

       

      I want the calculation to return:

      Measure1 penetration: 0%,10%,30%,30%,70%80%,90%,100%

      Measure2 penetration: 0%,7%,27%,93%,97%,100%

       

      This is essentially a (<value>-MIN)/(MAX-MIN), but I am unsure how to accomplish this in tableau.

       

      In this case, the global MAX for a given measure would always be 100% and the global MIN would be 0%, everything in between is a percent penetration of that range.

       

      How can I accomplish this? Is there a better way to make the color scales independent by measure/row?

        • 1. Re: How can I calculate range penetration %?
          ailsa.zheng

          Hi Mitchell,

           

          Here is the calculated field for measure 1's penetration:

           

          (attr([Measure 1])-WINDOW_MIN(min([Measure 1])))/(WINDOW_MAX(max([Measure 1]))-WINDOW_MIN(min([Measure 1])))

           

          Replace Measure 1 with Measure 2 for measure 2's penetration. I put measure 1 on the first sheet and measure 2 on the second.

          • 2. Re: How can I calculate range penetration %?
            Mitchell Gulbransen

            Unfortunately, I am running Tableau 8, so I couldn't view your sample workbook, but thanks for your quick response!

             

            I tried your calculation above and it did not return results with my current data setup. I then changed the ATTR([measure1]) SUM([measure1]), and I got closer, but the values don't look correct.

             

            What I am ultimately struggling with is how to create a common range so a color scale can be applied for a heat map without skewing towards the measures that naturally have higher values. This requires comparing the SUM of the measures by month to the MIN and the MAX of the full set of measures - I have yet to have success with this.

             

            Viz purists, not to worry, this is a short-term solution until we can get a more Tableau-friendly tall data structure, but for now, this is what we have to work with. I have attached a screenshot of a mockup of what our source data looks like (with randomized underlying data) for the time being, as well as my unsuccessful attempt to get range penetration %, displayed here in a table with two measures just to see if the calculation was looking right (which it was not)

             

            mock data penetration%.PNG

             

            penetration% unsuccessful.PNG

            • 3. Re: How can I calculate range penetration %?
              ailsa.zheng

              Hi Mitchel,

               

              Here is what my data looked like:

               

              measure1.png

              measure2.png

               

              So I believe the difference may be because mine calculated vertically and yours calculated horizontally. Can you try selecting the Edit Table Calculation and choosing Across rather than Down?

              • 4. Re: How can I calculate range penetration %?
                Mitchell Gulbransen

                Interesting, I haven't been able to get a view like that on the data. This is what I have for the time being:

                penetration% unsuccessful2.PNG

                 

                Still relatively new to Tableau, only been using for a month or so, so it could be something blatantly obvious I'm missing.

                • 5. Re: How can I calculate range penetration %?
                  ailsa.zheng

                  It looks like it's calculating using Table Down instead of Table Across, because if it's not getting results at all it might be doing this for February:

                   

                  (43-35)/(43-35)

                   

                  Instead of (43-40)/(55-40)

                   

                  Can you go to Edit Calculation and then play with the Calculate Along?

                  • 6. Re: How can I calculate range penetration %?
                    Mitchell Gulbransen

                    It is running table across I think?

                    Using ATTR([measure 1]):

                    penetration% unsuccessful4.PNG

                     

                    Using SUM([measure 1]):

                    penetration% unsuccessful3.PNG

                     

                     

                    But yes, (43-40)/(55-40) is exactly what I want to get to. Starting to question if this is even the way I want to go about solving my root problem of independently color scaling each row in a heat map.