5 Replies Latest reply on Sep 28, 2017 1:19 PM by Rick Maly

    Credits/Debits as a Percentage

    Rick Maly

      Hey everyone and thanks in advance for any help.

       

      Currently, I have three tables of data. 

       

      They are:

      1) Credits

      2) Debits

      3) Adjusted AR

       

      We have a metric where we divide both Credits and Debits by the Adjusted AR to get a percentage that is over 90 days.  I got the formulas set up with calculated fields to take everything over 90 days and then divide by the Adjusted AR.

       

      I am stuck on cleanup on two final things.  First, because it is dealing with money, there are negative percentages in some of the calculations.  Is there a way to force these percentages to all be positive?  The second question is some of the percentages go over 100%.  Is there a way to cap the percentages at 100 and not go over?

       

      Thanks again.

        • 1. Re: Credits/Debits as a Percentage
          Shinichiro Murakami

          Hi Rick,

           

          Please attach packaged workbook, or at least some data with excel or something.

           

          Thanks,

          Shin

          • 2. Re: Credits/Debits as a Percentage
            Andrew Kim

            Hi Rick,

             

            You just need a couple of simple calculations to get what you're looking for.

             

            Your first concern: "First, because it is dealing with money, there are negative percentages in some of the calculations.  Is there a way to force these percentages to all be positive?"

            Is solved by:

            CreditsDebits as a Percentage 1.PNG

            *You can replace Profit Ratio with whichever percentage measure you're working with.

             

            Your second concern: "The second question is some of the percentages go over 100%.  Is there a way to cap the percentages at 100 and not go over?"

            Is solved by:

            CreditsDebits as a Percentage 2.PNG

             

            To get values that look like:

            CreditsDebits as a Percentage 3.PNG

             

            As you can see for Order ID 'CA-2014-101147' the original profit ratio was -265% which is then converted to a positive by Absolute Profit Ratio to produce 265% which is then capped by Profit Ratio Cap to produce 100%.

             

            I've attached the packaged workbook. I saved this workbook in 10.3, however, this solution will work with any Tableau version.

            1 of 1 people found this helpful
            • 3. Re: Credits/Debits as a Percentage
              Rick Maly

              Thanks Andrew.

               

              I knew it was probably way easier than I was making it.  Playing around with Tableau was much as I was, I was just getting deep in the quick sand and thinking it was more difficult than it should be.

               

              This worked perfectly.

              • 4. Re: Credits/Debits as a Percentage
                Andrew Kim

                No problem Rick, I have that same issue too sometimes!

                 

                Please mark the answer as correct whenever you get a chance.

                • 5. Re: Credits/Debits as a Percentage
                  Rick Maly

                  So maybe you can help me with the charts as they seem to be different using your approach above.

                   

                  When I create a chart, the percentages are correct: 

                   

                  However, I am trying to create a line graph where you can select the teams.  Now when I have all the team combined, the line graph percentage is correct:

                   

                  However, when I filter down to a team, it shows up incorrect. This graph should match the 1st chart.

                  Not sure why this is calculating differently.