Okay, so this is a lot of work to get to your final solution, but I think I've figured out a way to do this. It's all pretty much just Tableau trickery.
First of all, we need union the data to itself, which will duplicate the data. Next, we'll use the new Table Name field to create an artificial Market name:
Market New
// Adjusted market name.
IF [Table Name]="Sheet1" THEN
[Market]
ELSE
[Region] + " Other"
END
Then we'll create calculated fields to get some additional details, ultimately getting to Amount New which will give us either the amount for the market or for "Other".
Total Amount
// Get the total overall amount for the region.
// Dividing by 2 to account for data duplication from the union.
{FIXED [Region]: SUM([Amount]/2)}
Other Amount
// Get the amount for "Other"
MAX([Total Amount])-SUM([Amount])
Amount New
// Amount depending on if it is a regular region or "Other"
IF RIGHT(ATTR([Market New]),5)="Other" THEN
[Other Amount]
ELSE
SUM([Amount])
END
Then we can build a view based on Market New and Amount New.
The trick here is that our filter is on Market and the union is handling the creation of the "Other" segment. You'll also want a filter on Amount New to include anything >0. Without this, the "Other" segment will always show even if all markets in that region are selected.
Take a look at it in action:
See attached.
Jan 28, 2019 8:44 AM
