9 Replies Latest reply on Nov 8, 2017 2:45 PM by Galen Busch

# How to create calculated field of field2 for each field1 in percentage.

Hi,

I have a simple service table and its location (second field) . How do I create a calculated field (field3) that represent the location percent for that service ?

Below is the sample table.

 Service1 1 25% Service1 1 25% Service1 2 50% Service4 3 75% Service4 3 75% Service4 3 75% Service4 4 25% Service2 2 50% Service2 3 50% Service3 4 100%
• ###### 1. Re: How to create calculated field of field2 for each field1 in percentage.

I don't understand the question.

Given that table, you already have numbers for each Service and Location.  What else are you looking to compute?

• ###### 2. Re: How to create calculated field of field2 for each field1 in percentage.

{FIXED [Service],[Location] : sum([Number of Records])}/{FIXED [Service] : sum([Number of Records])}

• ###### 3. Re: How to create calculated field of field2 for each field1 in percentage.

Here it is:

• ###### 4. Re: How to create calculated field of field2 for each field1 in percentage.

I think the calculations are wrong. Once i replace the simple number with higher numbers the calculations are off.

 Service1 6 Service1 6 Service1 7 Service1 2 Service4 3 Service4 3 Service4 3 Service4 4 Service2 2 Service2 3 Service3 4

• ###### 5. Re: How to create calculated field of field2 for each field1 in percentage.

It will be nice if you write some explanation or breakdown of your answer.

also some details as to what is the use of ":"

• ###### 6. Re: How to create calculated field of field2 for each field1 in percentage.

Yes my Cal was wrong. I just did not read properly what you need, but looks you got what you wanted

Thanks

Deepak

• ###### 7. Re: How to create calculated field of field2 for each field1 in percentage.

Sure.

There are 2 parts to this calculation:

This formula returns the number of records for each combination of [Service] and [location]. I use this as the numerator to calculate a percentage.

{FIXED [Service],[Location] : sum([Number of Records])}

The rows it generates are:

Service 1, Location 1, 2 records

Service 1, Location 2, 1 record

Service 4, Location 3, 2 seconds

Service 4, Location 4, 1 record

and so on.

This next formula generates one record per [Service]. I use this as the denominator.

/{FIXED [Service] : sum([Number of Records])}

The rows it generates are:

Service 1, 3 records

Service 4, 4 records

Service 2, 2 records

service 3, 1 record

So for Service 1/Location 1, the numerator is 2, and for Service 1, the denominator is 3, returning 66%.

The way to understand this calculation - let's break it apart

{ represents a level of detail calculation

[Service] and [location] represent the level of detail (grain) of your result set. I'm asking for one record for each combination of [Service] and [Location]

: denotes the separation between the partitioning dimensions and the aggregate measure

SUM( define your aggregation when manipulating grain of data

[Number of Records] represent the measure I am aggregating

)} close the calculation

• ###### 8. Re: How to create calculated field of field2 for each field1 in percentage.

How does it get converted to percent.

• ###### 9. Re: How to create calculated field of field2 for each field1 in percentage.

[Numerator]/[Denominator] returns a percent.

2/4 = .5

You can then format as percent in Tableau.

The level of detail calculations outlined above set the 'grain' for the numerator and the denominator.