Is it possible to calculate grand total base on the 'Showed items' only

[CHUN KIT LEE]

Hi all,

I want to make a cross table that only display the records below 92.5%, therefore I added a rule in 'Show/Hide Items'.

The problem is that those 'hidden items' are still counted in the grand totals.

As the image attached below, after adding the rule,

Grand totals of call = 503 + 708 = 1873 (which is incorrect)

Is there a way to solve this problem

Many thanks.

[CHUN KIT LEE]

Also, is it possible to set colours on grand total row/column

[David Boot-Olazabal]

Hi,

See my attached pictures for how you can calculate the grand totals based on the cell values rather than the underlying values (which is the default).

In my example, you can see that the state of Illinois has a total of 2110 (2100 + 10) when I have no rule applied. when I apply my rule (hide every value under 25), the 10 is not showing anymore. But is still counted for in the Grand total. Unless I select the Grand total to be based upon the cell values. Then you see the correct Grand total figure of 2100.

When it comes to coloring, please (left-mouse) click on the Grand total header and select the approriate color as well as checking the box for 'Stylethis column individually'. That should color your Grand total column (same is true for Grand total row).

Kind regards,

David Boot-Olazabal

[CHUN KIT LEE]

Thanks, what if I want to calculate the overall percentage. Is there any way to set the total as avg of cell values

[David Boot-Olazabal]

Not quite sure if that would be an option. You may need to approach this differently in that case. Perhaps pivot/unpivot transformations and calculated columns is then what you could use to achieve the overall percentage based on all values whereas the grand total is based upon cell values.

 

Kind regards,

 

David