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:
Code
Ins. A Revenue
Ins. A Cases
Ins. A Rate/Case
Ins. B Revenue
Ins. B Cases
Ins. B Rate/Case
% of Ins. A
100
$ 2,815
1
$ 2,815
$ 5,325
2
$ 2,663
94.58%
101
-
-
-
$ 5,276
2
$ 2,638
-
102
$ 3,240
2
$ 1,620
$ 9,546
6
$ 1,591
98.21%
103
$ 6,233
3
$ 2,078
$ 11,546
7
$ 1,649
79.39%
104
$ 1,842
1
$ 1,842
$ 3,748
3
$ 1,249
67.82%
Total
$ 14,130
7
$ 2,019
$ 35,441
20
$ 1,772
87.79%
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:
