Set analysis sum formula

[#1]

hello all,

I am trying to create a pivot table, showing the share of each customer's sales vs their region total sales.

The table should look like following

Region	Customer	Sales	Share
France	Customer 1	100	50%
France	Customer 2	100	50%
Germany	Customer 3	100	33%
Germany	Customer 4	200	66%
Italy	Customer 5	500	100%

Now I am not sure how to create the expression for the share field.

The challange for me is to calculate the total sales for each region as an aggregation of all customers sales per region.

Afterwards I would devide the individual customer's sales by that regional sales amount.

Here I got stuck. All the sum or aggr formulas I tried show basically the customers sales and not the regional sales amounts.

[kaushiknsolanki]

Hi,

     Try this Expression.

     sum(Sales)/Sum(TOTAL <Region> Sales)

Regards,

Kaushik Solanki

[vikasmahajan]

Create vTotalValue variable for total sales

and apply following formula for calculate % share

=SUM({$<FinancialYear=,CORPORATE=,Date={">=$(=max(YearStart))<=$(=max(YearEnd))"}>}Values/10000000)/vTotalValue

Hope  this help you.

Vikas

[#4]

Hi Kaushik,

this goes into the right direction but needs some adjustment.

So in general, when the pivot table is fully expanded, the values are fine.

However when I select one single customer from the pivot table the expression returns result, which is not wanted,e g.

Customer 1 selected

Region      Customer       Sales          Share

France       Customer1          100            100%

Here, I would have expected to still show the original share value (50%).

So in this case, the formula is aggregating the regional sales, based on the current selected customer.

Is there modification, I can apply to avoid this effect?