# Creating calculated fields in multi-columned table with several subrows

**Joe Applegate**Oct 3, 2018 4:21 AM

Hi,

I have a data set that has various ISBN numbers (think of them as product IDs) in various specific locations (signified by location code) and Region. The Calculated Volume column is the cubic volume of the location in that region, and the aggregate volume is the cubic volume of the # of ISBNs in that location (number of items in location x unit volume).

ISBN | Location Code | Region | Calculated Volume | Aggregate Volume | Number of Items in location |

11142 | 1010 | A | 57,000 | 24,000 | 1 |

1011 | B | 99,000 | 24,000 | 1 | |

11143 | 3400 | C | 40,000 | 10,000 | 1 |

3500 | D | 60,000 | 30,000 | 3 | |

11144 | 4100 | E | 30,000 | 12,000 | 4 |

4101 | E | 30,000 | 15,000 | 5 | |

4103 | E | 30,000 | 15,000 | 5 |

This is a bit complicated so bear with me... I would like to know if certain location codes are eligible to be consolidated to. Basically, can we shuffle around the same item that is spread out in various locations into 1 location to save space. More specifically, I'd like to know if the SUM of the Aggregate Volumes within an item/ISBN is LESS than the Calculated Volume of the Location Code with the higher Number of Items in it. If so, that location get's an "Eligible"

If the Number of Items in a Location are EQUAL, then the SUM of the Aggregate Volumes should be compared to the greater Calculated Volume among the group of Location Codes. If the SUM of the Aggregate Volumes is less than that higher Calculated Volume, the Location code with the higher Calculated Volume is "Eligible".

**Desired Output:**

ISBN | Location Code | Eligibility | Region | Calculated Volume | Aggregate Volume | Number of Items in location |

11142 | 1010 | Null | A | 47,000 | 24,000 | 1 |

1011 | Eligible | B | 99,000 | 24,000 | 1 | |

11143 | 3400 | Null | C | 40,000 | 10,000 | 1 |

3500 | Eligible | D | 60,000 | 30,000 | 3 | |

11144 | 4100 | Null | E | 30,000 | 12,000 | 4 |

4101 | Null | E | 30,000 | 15,000 | 5 | |

4103 | Null | E | 30,000 | 15,000 | 5 |

In ISBN 11142, the SUM of the Aggregate Volumes is 48,000. Since the Number of Items in Location is EQUAL to one another, the 48,000 sum is compared to the greater Calculated Volume within that ISBN, which is 99,000. Since 48,000 < 99,000, Location Code 1011 is "Eligible".

In ISBN 11143, the SUM of the Aggregate Volumes is 40,000. This is compared to 60,000 since Location Code 3500 has more items in it than Location 3400. Since 40,000 < 60,000 Location Code 3500 is "Eligible".

In the final case of ISBN 11144, the SUM of the Aggregate Volumes is 42,000. This is compared to the Location Code with the higher Number of Items. In this case, that is either Location code 4101 or 4103. (I'm *not sure if we can make logic that says compare to either one, since it can't pick one with the greater calculated volume since they're in the same Region and equal), and thus it should be indifferent) *and since 42,000 > 30,000, all Location Codes for that ISBN are Null.

I know that's a lot of logic and I'm not sure if it can be executed in Tableau, but any help would be appreciated! I've been spinning my wheels for quite some time... I recreated my data set in these mock tables since what I'm working on can't exactly be shared, so I also appreciate the flexibility. Thank you!