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
Opened your workbook, Avishek - and it isn't the context, it's the AGGREGATION on the denominator. Solid setup otherwise.
Your _SubCatMktShr%WithContext is SUM([Sales]) / SUM([_SubCatTotalWithContext]), and _SubCatTotalWithContext is { FIXED : SUM([Sales]) } - the grand total. A FIXED like that puts the SAME grand-total value on every row. When you wrap it in SUM(), Tableau adds that constant up once per underlying row, so the denominator becomes grand-total x row-count (huge), the share collapses to a tiny number, AND it shifts every time you change the Sub-Category selection - because deselecting sub-cats removes rows, which changes the multiplier. That is the 'it moves / doesn't work' behaviour you're seeing.
The fix is basically one word - aggregate that FIXED denominator with MIN (or AVG / ATTR), never SUM:
_SubCatMktShr%WithContext = SUM([Sales]) / MIN([_SubCatTotalWithContext])
MIN of a constant just returns the constant, so the denominator stays the fixed, context-scoped market total, your multi-select Sub-Category filter only trims the numerator, and your context filters keep cascading. That is exactly the multi-select market share you're after.
Two more notes: you can retire the nested _SubCatTotal (the EXCLUDE { FIXED ... }) - it hits the same SUM-of-a-FIXED trap plus more complexity than you need; the MIN version above is enough. And if you want the share PER sub-category instead of combined, drop Sub-Category on Rows - the same calc holds.
Give that MIN swap a shot. If it finally behaves, a Best Answer mark would be great - and shout if the numbers still look off after it. 🙂