Using pivots subtotal (partial facts calculations)

[#1]

Hello,

Using pivot table as one of the ways to provide drill down.

Issue is that one of the base metrics that is used in consequent calculation is loaded as partial fact (utilized link table).

Here is an example.

Region    Market             Products       Membership    Sales   Sales PMPM*

East        Virginia            Prod1            4                      20            5.00   

                                       Prod2            7                      80            11.43

              Market Total:                        10                   100          10.00

               Philadelfia        Prod2           15                    500           33.33

                                       Prod3            85                   1000          11.76

              Market Total:                       100                 1500          15.00

Region Total                                       110                 1600          14.55

So membership is loaded as partial fact, sales data is normal.

Sales PMPM (per member per month) highlighted in red is not quite what I need here. The need is to use Market Subtotal as a base for Product PMPM calculation.

Can it be done and how?

[sunny_talwar · Accepted solution]

So you need to get 100/4 = 25 for Prod1 and 100/7 = xxx fpr prod2?

Try this:

Sum(TOTAL <Region, Market>Sales)/Sum(Membership)

[#3]

Hi Sunny,

Thank you for the solution! It worked.

Actually needed 20/10 = 2 for Prod1 and 80/10 = 8 for Prod2. So 2 + 8 = 10.

SUM(Sales)/SUM(TOTAL <Region,Market>Membership)