I have a use case where I need to calculate Market Share % without relying on a Context Filter + Dimension Filter approach.
For example, Sub-Category Market Share should be calculated as:
Sales for the selected Sub-Category ÷ Total Market Sales (across all Sub-Categories), while still respecting the other filters applied by the user.
The conventional approach is to keep Sub-Category as a Dimension Filter and make all other relevant filters Context Filters. The Total Market can then be calculated using:
{ SUM([Sales]) }
While this approach works, it does not meet our business requirement. We have additional filters that need to be applied after the Sub-Category selection. Due to Tableau's order of operations, a Dimension Filter on Sub-Category does not make these downstream Context Filters dynamically relevant based on the selected hierarchy.
We therefore explored the usual explicit LOD approach, where the denominator for Total Market is defined using a FIXED LOD, for example:
{ FIXED [Dim 1], [Dim 2], ... : SUM([Sales]) }
However, we are facing an issue with this approach: as soon as a Sub-Category is selected, the Total Market value unexpectedly decreases, instead of remaining at the total market level across all Sub-Categories.
There is more detail on the specific scenario and expected behavior in the sheet caption within the attached workbook
Kindly help me in getting the LOD method working...
#Tableau Cloud #Tableau Desktop & Web Authoring
Hi Avishek - this is baked into Tableau's order of operations. { SUM(Sales) } is a FIXED LOD, and FIXED computes BEFORE dimension filters - it ignores every filter except Context/data-source/extract. That is exactly why you were told to convert the others to Context filters.
Two ways out, no Context filters needed:
1. Switch FIXED to EXCLUDE: { EXCLUDE [Sub-Category] : SUM([Sales]) }. INCLUDE/EXCLUDE compute AFTER dimension filters, so this respects all your other filters while collapsing the total across sub-categories. The catch: EXCLUDE can only total the sub-categories still in the data - if Sub-Category is a dimension FILTER, the filtered-out ones are already gone, so the total market would not include them.
2. So the robust pattern: do NOT filter Sub-Category at all - select it with a Parameter (or a Set). Then every sub-category stays in the data, and:
- Denominator = SUM([Sales]) (or the EXCLUDE above) = true total market, still respecting your other dimension filters.
- Numerator = SUM( IF [Sub-Category] = [Selected Sub-Cat Param] THEN [Sales] END ).
- Market Share = Numerator / Denominator.
Because the selection is a parameter, not a filter, nothing is removed from the denominator and your downstream filters still apply normally - which is the behaviour you are after.
If this helps, please mark it as the Best Answer so it helps the next person - thanks :)
