4 Replies Latest reply on Dec 12, 2016 7:25 AM by Tom W

    Dynamic Formulas?

    Steve Atkinson

      Hi All,

      I'm not sure a dynamic formula is a solution, but it came to mind as potential (only I don't know if it's possible in Tableau).

      My dataset is simple (image of dummy data below). A row for each client, a column for client ID, and all other columns are binary variables (values of only 0 and 1).

       

      When loaded into Tableau, all of my variables (columns) are by default Measures. I would like to calculate and display percentages for these measures (summing up each column, as a percent of total number of observations/clients). I would prefer not to create a calculated field for each variable/measure (as there are many columns, and this gets lost when I update my dataset), and am wondering if it's possible to create one generic calculated field, that I could then operate with a filter to display percentage for whichever variable I'm interested in. I somehow think parameters comes into play here, but not certain.

       

      Any thoughts on this?
      Thanks,
      Steve

      2016-12-07_14-14-43.jpg

        • 1. Re: Dynamic Formulas?
          Tom W

          If you bring in your data in a column like approach as per the example, you're going to have to specify each individual column name in any formula you build.

          Instead, you would be better off with a structure like;

           

          ClientID, VariableName, Value

          100010, Variable1, 1

          100010, Variable2, 0

           

          etc.

           

          This would help you keep things more dynamic. You can reshape your data externally or take a look into the pivot function when connecting to your data.

          1 of 1 people found this helpful
          • 2. Re: Dynamic Formulas?
            Keshia Rose

            Hi Steve,

             

            You could use a parameter to create a couple calculations that would allow you to show the percentage for each variable. However, I agree with Tom that a pivoted data structure would make it easier. I've attached a workbook that shows both methods so you can take a look at a couple of ways to do this. Here are the methods:

             

            Original Structure:

            1. Create a parameter that has each variable listed

            2. Create a logical calculation that will flip between each measure based on the parameter

            3. Create the percentage calculation, using the logical calculation above

            4. Show parameter and use

             

            Pivoted Structure:

            1. Pivot the data in Tableau or beforehand

            2. Create the percentage calculation

            3. Show filter and use

             

            Let me know what you think.

             

            Take care,

            Keshia

            • 3. Re: Dynamic Formulas?
              Steve Atkinson

              Thank you, Tom, this worked brilliantly! I had not seen this function before, and now that I know about it, it's going to save me a lot of up-front time.

              Much appreciated!

              Steve

              • 4. Re: Dynamic Formulas?
                Tom W

                No problem Steve, I'm glad I could help out.

                Please be sure to mark the answer as Correct going forward to close out the thread.