Stephane Forrer Posted February 28, 2023 Share Posted February 28, 2023 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 Link to comment Share on other sites More sharing options...
Solution Gaia Paolini Posted March 1, 2023 Solution Share Posted March 1, 2023 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 columnCount() / 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. Link to comment Share on other sites More sharing options...
Stephane Forrer Posted March 2, 2023 Author Share Posted March 2, 2023 This worked a charm Gaia ... thank you!! ⭐️⭐️⭐️⭐️⭐️ Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now