Static Sub-totals when filtering a crosstable

[Stephane Forrer]

Hi,

I have a cross table similar to below... does anyone know how do I keep % total the same regardless of the filtering applied?

Body System | Term | Subjects | % Total | Instances|-------------|----------|----------|---------|----------|Eye Disorder | Dry Eye | 1 | 16.6 % | 1 | | Pink Eye | 2 | 33.3 % | 1 | | Subtotal | 3 | 50 % | 3 | Leg Disorder | Dry Leg | 1 | 16.6 % | 1 | | Pink Leg | 2 | 33.3 % | 2 | | Subtotal | 3 | 50 % | 3 |  Fltered to Leg Disorder it re-calcs the % of total based on the filtered values not all values:Body System | Term | Subjects | % Total | Instances|-------------|----------|----------|---------|----------|Leg Disorder | Dry Leg | 1 | 33.3 % | 1 | | Pink Leg | 2 | 66.6 % | 2 | | Subtotal | 3 | 100 % | 3 |   Filtered to Leg Disorder I want to remain as :Body System | Term | Subjects | % Total | Instances|-------------|----------|----------|---------|----------|Leg Disorder | Dry Leg | 1 | 16.6 % | 1 | | Pink Leg | 2 | 33.3 % | 2 | | Subtotal | 3 | 50 % | 3 |

The expression for the % Total at the moment is:

Count() THEN [Value] / Sum([Value]) as [% Total] 

Any ideas? & Thanks!

Stéphane

[Gaia Paolini]

The only way I could find to see only the subset of filtered data and to preserve the percentages was to define a calculated column to represent your total count. Calculated columns do not react to filters.

So if you change your expression to this, where [TotalTotal] = Count() was your new calculated column

Count() / Max([TotalTotal]) as [%Total]

... then it should work. I had to add Max() as it wants an aggregation.

You could try using a dropdown selection of the Body System and then setting a rule in Show/Hide items, but I could only make it work if only one Body System is selected each time.

[Stephane Forrer]

This worked a charm Gaia ... thank you!!

⭐️⭐️⭐️⭐️⭐️