1 Reply Latest reply on Jan 3, 2019 2:57 PM by Patrick Van Der Hyde

# Problems calculating a percentage from fields in separate tables

Hi,

Apologies that I can’t post a twbx, due to data confidentiality, but let me try to explain. I’m a novice with calculated fields, so it’s easy enough to lay out the problem, because for most of you, it’s probably simple.

I have two data sources, let’s call them STUFF and THINGS. Each contains numerous data fields, some of which appear in both sources, but most are specific to a single table.

So, I’m located in the STUFF table, and want to create a calculated field to produce a percentage, where the numerator field (let’s call it field1) is in THINGS, but the denominator (let’s call it field2) is in STUFF (where I am).

There’s a condition to be factored in. I only want to include some of the possible records of field1 in the calculation. Field1 stores values of “t” for true, and “f” for false. I’m only interested in the true values.

As far as my limited knowledge takes me, this should look something like:

COUNT ( [THINGS].[field1] = "t" ) / COUNT ( [field2] )

I know about setting the Default properties/Number format of the calculated field to Percentage, so I believe I don’t need to explicitly multiply by 100. I think COUNT is the appropriate function, as the records in field2 are by definition unique.

Desktop tells me this is a valid formulation, but there are two problems when I use the calculated field:

First, I get results for both “t” and “f”, as opposed to just the “t” values, which is what I want to count. Second, the “percentages” returned are wildly off where they should be. I suspect the way I’ve expressed the “t”-only condition to be a cause of the first problem. I don’t know what the second problem is caused by, unless it’s something to do with the way I’ve identified the numerator field, which is located in the other table. Or should I be building a formal connection between the two tables, which I have not consciously done, assuming that [THINGS].[field1] is an adequate pointer from one table to the second.

I’m using Tableau Desktop 2018.2.

Can anyone help?

Thanks.

XP.

• ###### 1. Re: Problems calculating a percentage from fields in separate tables

Hello Xavier,

You will find that a small package workbook with some sample data - (just make something up in excel to mimic your data) will help the community to engage and assist.  Sorry for the late reply.

I would probably change - ( [THINGS].[field1] = "t" )  to If ( [THINGS].[field1] = "t" ) then  1 end  and then wrap a sum() around that field in a calculation over your count of field 2.   Note that I would likely double check on the next count statement as well.  Without a view of the data you are working with, it is hard to provide speicific calculations.

Packaged workbooks and flows: when, why, how

thank you

Patrick