OK, this could be difficult to explain, so bear with me. I have a pivot table that uses set analysis and/or alternate states in most of its columns. The user can select Insurer A and Insurer B to compare the two's rates for particular codes. The table looks like this:
Ins. A Revenue
Ins. A Cases
Ins. A Rate/Case
Ins. B Revenue
Ins. B Cases
Ins. B Rate/Case
% of Ins. A
The last column should show Ins. B's Rate/Case divided by Ins. A's Rate/Case, as a percentage.
It works great when the data is complete. However, note that Ins. A has no cases for Code 101. This skews the grand total in the "% of Ins. A" column because it includes Ins. B's data for Code 101.
What I need to do is exclude Code 101 when figuring out Ins. B's Rate/Case, but only in the total row for "% of Ins. A." When I do so manually, the total "Ins. B Sum" becomes $30,165, divided by the total "Ins. B Cases" of 18, which gives me a total "Ins. B Rate/Case" of $1,676, for a "% of Ins. A" of 83.02%, not the 87.79% shown. (Note that I don't want to exclude Code 101's data from the other columns in the Total row, only the "% of Ins A" column.)
I never know in advance what two insurers the user will select, so I don't know if Ins. A or Ins. B will have no cases for a particular code. I have to allow for either possiblity.
The drop-down list from which the user selects Ins. A is set to the alternate state "InsA." There is no alternate state for Ins B. To further complicate matters, Ins. A can include any Classes the user has selected, while Ins. B can include only Classes 1 and 2.
The expression I use for the "% of Ins A." column is this: