Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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)
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)