Category = Furniture is a
Calculated field: Share of region
SUM([Sales]) /
MIN({FIXED [Region] :
SUM([Sales])})
Orders
| Region | Category | Sales |
|---|---|---|
| East | Furniture | 120 |
| East | Technology | 300 |
| East | Office | 80 |
| West | Furniture | 200 |
| West | Technology | 150 |
| West | Office | 50 |
| South | Furniture | 90 |
| South | Technology | 210 |
The same view as SQL
Result
| Region | Sales | Total | Share |
|---|
flip it: the WHERE climbs into the totals
What happens
Tableau applies filters in a fixed order. Context filters run first, then FIXED expressions are computed, then dimension filters remove rows from the view. So the FIXED total per region is built from every category unless the Category filter runs before it.
For a share of total, that's what you want: Furniture is 24% of East. When the total should shrink with the filter, it isn't.
The same thing in Power BI
A DAX measure gets the dimension-filter behaviour by clearing every filter on the table except Region:
Share of region :=
DIVIDE (
SUM ( Orders[Sales] ),
CALCULATE ( SUM ( Orders[Sales] ),
ALLEXCEPT ( Orders, Orders[Region] ) )
)
For the context-filter behaviour, keep Category too: ALLEXCEPT ( Orders, Orders[Region], Orders[Category] ).
Traps
- SUM around a FIXED double-counts. The FIXED value repeats on every row of its region, so aggregate it with MIN, MAX or AVG.
- Data source and extract filters always run first. They shrink the FIXED total whether or not you use context.
- INCLUDE and EXCLUDE are different. They're computed after dimension filters, so they already respect Category.