Hi, everyone,
I have a pivot table with 3 dimensions.
Country
Productgroup
Product
In addition, I have a few key figures such as quantity, order intake and sales.
I would now like to have the percentage of sales for the product groups per country.
For this I used the following formula:
SUM(Sales)
/
SUM(TOTAL<Productgroup>Sales)
If all levels of the pivot table are collapsed and only the country is displayed, there are different percentages % (total 100%). If I open the product group level in just one country, it's 100% everywhere. Only when I open all levels do I get shares. However, not the shares of the product group per country, but the share of the product group related to all countries.
Example:
No level expanded
Country and product group level expanded
| Germany |
Group 1 |
100% |
| |
Group2 |
100% |
| Italy |
Group 1 |
100% |
| |
Group 2 |
100% |
All levels expanded
| Germany |
Group 1 |
Product 1 |
20% |
| |
|
Product 2 |
20% |
| |
|
Product 3 |
25% |
| |
Group 2 |
Product 4 |
10% |
| |
|
Product 5 |
15% |
| |
|
Product 6 |
25% |
| |
|
Product 7 |
8% |
| Italy |
Group 1 |
Product 1 |
35% |
| |
Group 2 |
Product 2 |
42% |
The result should look like this:
| Germany |
Group 1 |
65% |
| |
Group 2 |
35% |
| |
|
100% (only comment for you) |
| Italy |
Group 1 |
58% |
| |
Group 2 |
42% |
| |
|
100% (only comment for you) |
Can you help me?
