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:
(SUM({InsA<@ID=P({$<@Class={$(vClass)},>}@ID)>}Revenue)
/SUM({InsA<@ID=P({$<@Class={$(vClass)}=>}@ID)>}Cases))
/
(SUM({$<@ID=P({$<@Class={1,2}=>}@ID)>}Revenue)
/SUM({$<@ID=P({$<@Class={1,2}>}@ID)>}Cases))
It works great on every row except the total. Any suggestions?
