4 Replies Latest reply on Nov 17, 2016 1:48 PM by Bhavana Gallewale

    parameter with multiple number formats for reference lines

    Bhavana Gallewale

      hi,

      I have a parameter toggling between Total $ and Total % with a need to show one value as $K and one value as %. Implemented showing properly formatted values in stack bars.

       

      However now need to add reference lines for totals like displayed in the screen shot below for each selection showing proper formatting for $ and % depending upon parameter selection. Can someone help me understand how to do this? (reference lines should show $20,285K for Total $ option and 100% when Total % option is chosen)

       

       

       

       

       

      Thank you,

      Bhavana

        • 1. Re: parameter with multiple number formats for reference lines
          Simon Runc

          Hi Bhavana,

           

          So one way to do this is to set up 2 different reference lines, where one of them equates to NULL, when not needed. This means we can format them both seperatley.

           

          In the attached (you didn't specify your Tableau version, so I've gone with 9.3!) I've set up a parameter which lets you select the measure as Sales or Share of Sales (simialr to the image you posted)...and have the Axis measure set up in the usual "measure selector" way.

           

          I then create 2 further calculations...

          [Sales Ref Line]

          CASE [Sales or Share Percent]

              WHEN 1 THEN SUM([Sales])

          END

           

          and

          [Share Ref Line]

          CASE [Sales or Share Percent]

              WHEN 2 THEN SUM([Sales])/TOTAL(SUM([Sales]))

          END

           

          notice that for each, when the parameter is set to the other measure they equate to NULL.

           

          i can then bring both these onto the detail pane, which gives me access to them in the reference lines. I then set up 2 reference lines (one based on each of these), and set the formatting, so one shows as £ and the other as %....then as we change the parameter we get the behaviour we want.

           

          Hope this does the job, and makes sense...let me know if not.

          • 2. Re: parameter with multiple number formats for reference lines
            Bhavana Gallewale

            Thank you so much Simon!!

             

            Solution seems to be working mostly however there are a few details that seem to be missing. Looks like measures are put on detail shelf separately for putting together 2 separate reference lines. In that case, is there a way to change formatting mark label formatting? : e.g. in the following screenshot it should show mark labels in % format,

             

             

            or if you choose Sales, it would show mark labels not just as numbers, but with $ sign?

             

             

             

            thank you,

            Bhavana

            • 3. Re: parameter with multiple number formats for reference lines
              Simon Runc

              Glad it helped

               

              ...so the way to do this for Labels and ToolTips is pretty similar. They both take advantage of the fact that Tableau doesn't (well no one can!!) plot NULLs

               

              So for your Labels...

               

              You can bring both the fields we created for the Reference Lines onto the Label shelf (again formatted differently) and then edit the Label so they are both side by side. As one will always be NULL, it will only ever plot one at a time.

               

              and again you can do exactly the same for tooltips.

               

              This is a bit of a pain, so you might like to vote this idea up (would make life a lot easier!!)

               

              https://community.tableau.com/ideas/1411

              • 4. Re: parameter with multiple number formats for reference lines
                Bhavana Gallewale

                Thank you so much Simon!!

                 

                It totally worked. you are a godsend

                 

                thank you,

                Bhavana