This content has been marked as final. Show 2 replies
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?