3 Replies Latest reply on Sep 7, 2017 2:40 PM by Jennifer VonHagel

Creating a Calculated Percentage

Hey everyone. I'm building a table in Tableau (desktop version 10.3.2), and need to create a custom column that calculates a percentage, one column as a percentage of another, and I'm having some trouble. Here's the setup:

Column A is a count of Encounter IDs (could also be the Number of Records). Encounter ID's are ~7 digits, so they need to be counted instead of summed.

Column B is a sum of the number of those encounters that ended prematurely. It's a binary field, so it must be summed.

Column C is the column I need to create. It should be Column B divided by Column A, as a percentage, and it should take the filters into account.

I have a two different filters being used. The first limits the data set to only pull from a certain YM. The second limits it to a certain facility.

I tried taking the early column divided by both the number of records and the count of encounter IDs column, neither of which worked. It seemed to be ignoring my filters, and dividing the number of earlies by the total number of encounter IDs, not just the ones from a specific facility in a specific month. I tried making it as a Table Calculation and got a similar result.

The part that's really driving me crazy is that when I went to make a dummy data set so I could post a workbook on here, it works perfectly: AVG([early] / [number of records]). I checked every setting I could between the working example and the workbook I'm having trouble with and couldn't find any difference.  So anyway, since publicly posting company data seems like a sub-optimal life decision and all of my attempts at a dummy data set work, I'm afraid I'm not able to post anything to fully demonstrate my issue. I'll be happy to provide any additional information needed.

Thanks!

• 1. Re: Creating a Calculated Percentage

Have you tried turning it off and on again? Hahaha, just kidding. It's bizarre that this is working for your dummy data but not your real data.

From what you're describing, I would have expected that Column C would be calculated:

SUM([EARLY]) / COUNT([number of records])

Is there any chance your formula is first calculating something at the row level, and then aggregating the results of the row level calculations. Rather than first aggregating each column, and then dividing the aggregations? I tried to mock this idea up in Excel. Can you see the difference I mean?

Not sure this has anything to do with your issue, but thought I'd share.

Good luck!

Jennifer

• 2. Re: Creating a Calculated Percentage

Thanks for the response, Jennifer. Funnily enough, the first thing I tried was closing the program and reopening it, didn't work though. However, I just left it alone for two hours and figured I'd give it one last try before going home and for some reason it worked. As best I can tell, I did the exact same thing I had been doing. I'm just going to chalk this one up to gremlins and move on I guess!

• 3. Re: Creating a Calculated Percentage

Haha, Gremlins for the win.

Glad it worked out,

Jennifer