4 Replies Latest reply on Feb 21, 2019 1:04 PM by Emily Teekasingh

    How do I calculate a ratio of two different domains within a field?

    Andreas Linden

      Hi,

       

      I'm trying to graph the ratio between two domains within a field - for instance, I could have a table consisting of the fields 'date', 'product', and 'revenue'; the 'product' field data can either be 'Mac' or 'PC'. How would I go about setting up a calculated field to work out the ratio of these two product types?

       

      Many thanks

      Andy

        • 1. Re: How do I calculate a ratio of two different domains within a field?
          Shawn Wallwork

          (IF [Product]="Mac" THEN SUM([revenue]) END) / (IF [Product]="PC" THEN SUM([revenue]) END)

          • 2. Re: How do I calculate a ratio of two different domains within a field?
            Tracy Rodgers

            Hi Andreas,

             

            Shawn is right, however, you might have difficulty in getting the measure to populate.

             

            Therefore, separate the two clauses into 2 separate calculated fields:

             

            Mac Revenue:

            (IF [Product]="Mac" THEN ([revenue]) END)

             

            PC Revenue:

            (IF [Product]="PC" THEN ([revenue]) END)

             

            Then, create a third calculation to find the ratio:

             

            sum([Mac Revenue])/sum([PC Revenue])

             

            Hope this helps!

             

            -Tracy

            • 3. Re: How do I calculate a ratio of two different domains within a field?
              Andreas Linden

              Thanks Tracy, Shawn! I can't seem to make the final calculation work though - the ratios all end up as null! It appears that, for each product/month calculation, Tableau either calculates a PC revenue or a Mac revenue, and sets one of the two to null - which is fine when summing the revenues of one of the products, but makes it difficult to calculate a ratio using these calculated fields.Screenshot.jpg

              • 4. Re: How do I calculate a ratio of two different domains within a field?
                Emily Teekasingh

                Hi Andreas, I know this is now a SUPER old request, but I had the same problem and resolved it. Leaving this note in case it helps someone.

                 

                Your data looks normal in the "view data" window, it will not display a sum at the row level. However, once you put it on the graph, it should resolve the issue. Even after moving it to the graph, I had a problem where all of my values came up as null. Since I was graphing several metrics at the same time, Tableau had automatically used the same field for the graph's "Color" (the same thing would have happened if I'd also had it as a "detail"). The color in this case was associated with my equivalent of your Product field. Once I removed it from the color shelf, the issue resolved and my ratio displayed correctly. The problem was happening because technically, the ratio is not associated with either Product exclusively, so it displays a null value.

                 

                A table calculation using "Percent From" with the specific dimension of "Product" can also do something similar if you WANT to use whichever color you've associated with Product categories for the ratio graph. You can order how you'd like the ratio calculated (numerator vs. denominator) by re-ordering the values on the legend. Then, you can re-format the value if you don't want it as a percentage.

                 

                Unfortunately I don't have time to put together a workbook right now that doesn't use our proprietary data, but if it would benefit anyone let me know and I can make some time for it.