Contribution calculations are typically done using a combination of Measured Values and arithmetic operations.
Consider the following raw data:
| Product | Store | Month | Sales |
| Shoes | Store A | January | 10 |
| Shoes | Store A | February | 20 |
| Shirt | Store A | January | 30 |
| Shirt | Store B | February | 40 |
| Pants | Store A | January | 50 |
Contribution of Product Sales to Total Sales
We use the ALL function to indicate that [Sum Sales] needs to be calculated in the scope of all Members of the Products Dimension.
| [Product] | [Sum Sales] | ( [Sum Sales], ALL([Product]) ) |
| Shoes | 30 {10+20} | 150 {30+70+50} |
| Shirt | 70 {30+40} | 150 |
| Pants | 50 {50} | 150 |
Notice that the the value of this formula is identical for all Products. To calculate the contribution of sales to total sales:
| [Product] | [Sum Sales] / ( [Sum Sales], ALL([Product]) ) |
| Shoes | 0.20 (30/150) |
| Shirt | 0.47 (70/150) |
| Pants | 0.33 (50/150) |
Contribution of Product Sales to Total Sales of Filtered Products
Similarly, we can use a Filter inside a Measured Value to calculate a Measure in the scope of the Members returned by the Filter. Assume the Filter [Top 2 Products] returns the following results:
| [Top 2 Products] | [Sum Sales] |
| Shirt | 70 |
| Pants | 50 |
If we place this Filter inside a Measured Value, we can calculate total sales for the top 2 products:
| [Top 2 Products] | [Sum Sales] | ( [Sum Sales], [Top 2 Products] ) |
| Shirt | 70 | 120 (70+50) |
| Pants | 50 | 120 |
Notice that the the value of this formula is identical for all Products. To calculate the contribution of sales to total sales of the top 2 products:
| [Top 2 Products] | [Sum Sales] / ( [Sum Sales], [Top 2 Products] ) |
| Shirt | 0.58 {70/120} |
| Pants | 0.42 {50/120} |
Contribution of Product Sales Per Store to Total Product Sales
Assuming the raw data above, Product sales per store is:
| [Store] | [Product] | [Sum Sales] |
| Store A | Shoes | 30 |
| Store A | Shirt | 30 |
| Store A | Pants | 50 |
| Store B | Shirt | 40 |
We use the ALL function to indicate that [Sum Sales] needs to be calculated in the scope of all Members of the Product and Store Dimensions.
| [Store] | [Product] | [Sum Sales] | ( [Sum Sales], ALL([Product]), ALL([Store]) ) |
| Store A | Shoes | 30 | 150 {30+30+50+40} |
| Store A | Shirt | 30 | 150 |
| Store A | Pants | 50 | 150 |
| Store B | Shirt | 40 | 150 |
Notice that the the value of this formula is identical for all rows. To calculate the contribution of sales to total sales:
| [Store] | [Product] | [Sum Sales] / ( [Sum Sales], ALL([Product]), ALL([Store]) ) |
| Store A | Shoes | 0.20 {30/150} |
| Store A | Shirt | 0.20 {30/150} |
| Store A | Pants | 0.34 {50/150} |
| Store B | Shirt | 0.26 {40/150} |
See Also