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

Credits/Debits as a Percentage

Hey everyone and thanks in advance for any help.

Currently, I have three tables of data.

They are:

1) Credits

2) Debits

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

Hi Rick,

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

Thanks,

Shin

• 2. Re: Credits/Debits as a Percentage

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:

*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:

To get values that look like:

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

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

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

• 5. Re: Credits/Debits as a Percentage

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.