How to compute the marginal value with ActivePivot

From a dataset of transactions, I am trying to compute a marginal P&L, that is, the total P&L by product except for the contribution of a given buyer.

Consider the following dataset:

+---------+---------+--------+
| Product | Buyer | Amount |
+---------+---------+--------+
| Water | Arthur | 10 |
| Water | Georges | 25 |
| Water | Arthur | 13 |
| Water | Oli | 42 |
| Wool | Oli | 150 |
| Wool | Oli | 18 |
| Wool | Simon | 70 |
| Wool | Arthur | 20 |
| Wood | Georges | 231 |
+---------+---------+--------+



+---------+---------+--------+
| Product | Buyer | Amount |
+---------+---------+--------+
| Water | Arthur | 10 |
| Water | Georges | 25 |
| Water | Arthur | 13 |
| Water | Oli | 42 |
| Wool | Oli | 150 |
| Wool | Oli | 18 |
| Wool | Simon | 70 |
| Wool | Arthur | 20 |
| Wood | Georges | 231 |
+---------+---------+--------+


With additional transient columns, I expect the following result,

+---------+---------+--------+--------------------+--------------+
| Product | Buyer | Amount | (Total per product)|Marginal total|
+---------+---------+--------+--------------------+--------------+
| Water | Arthur | 10 | 90 | 48 |
| Water | Georges | 25 | 90 | 65 |
| Water | Arthur | 13 | 90 | 77 |
| Water | Oli | 42 | 90 | 48 |
| Wool | Oli | 150 | 258 | 90 |
| Wool | Oli | 18 | 258 | 90 |
| Wool | Simon | 70 | 258 | 188 |
| Wool | Arthur | 20 | 258 | 238 |
| Wood | Georges | 231 | 231 | 0 |
+---------+---------+--------+--------------------+--------------+



+---------+---------+--------+--------------------+--------------+
| Product | Buyer | Amount | (Total per product)|Marginal total|
+---------+---------+--------+--------------------+--------------+
| Water | Arthur | 10 | 90 | 48 |
| Water | Georges | 25 | 90 | 65 |
| Water | Arthur | 13 | 90 | 77 |
| Water | Oli | 42 | 90 | 48 |
| Wool | Oli | 150 | 258 | 90 |
| Wool | Oli | 18 | 258 | 90 |
| Wool | Simon | 70 | 258 | 188 |
| Wool | Arthur | 20 | 258 | 238 |
| Wood | Georges | 231 | 231 | 0 |
+---------+---------+--------+--------------------+--------------+


Many thanks

1 Answer
1

This can be achieved using the "drillup" function. With CoPPer, you can get the sum of the amounts at level Buyer and easily use the same aggregation function on the parent level (Product) with drillUp.

DrillUp actually creates a measure whose value is the value of another measure on a parent member.

Then, you just need to substract the total of a product/buyer to the total of the parent with "minus" to get the marginal P&L.

The result can be visualized by calling toCellSet and show with an MDX query on the dataset:

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.