1 Reply Latest reply: Jul 1, 2013 12:08 PM by Bill Bois RSS

    Dealing With Partial Data In Total Row

    Bill Bois

      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:

       

      CodeIns. A RevenueIns. A CasesIns. A Rate/CaseIns. B RevenueIns. B CasesIns. 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?