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:
It works great on every row except the total. Any suggestions?