% Sales contribution Wrt first dimension

[ashish_2511]

Dear community,

Below are my input and Output tables.. Please suggest the best way to achieve this -

Input -

Region	Sub-Region	Sales
APAC	China	123
APAC	India	521
APAC	Pakistan	214
LATAM	Brazil	302
LATAM	Argentina	325
LATAM	Uruguay	142

Output -

- Region and Sub-region are taken as dimensions

- Chart type - pivot table

- %sales contribution formula (for china let's say) = 123/(123+521+214), in other words - Sum of sales of sub-region / Total sales of the respective region

Thanks in advance

-Sneh

[gowtham_patnaik]

try to add  %sales contribution /region  as calculated dimension (instead of expression).

[prat1507]

Hi

Please find the attached app for your reference.

Regards

Pratyush

[devarasu07]

Hi,

PFA, Try like below,

Sales Contribution %

=Sum(Sales)/aggr(sum(Sales),Region)

Thanks, Deva

[miskin_m]

Hi,

Try this expression---

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

[vinieme12 · Accepted solution]

Sum (Sales)/Sum (Total <Region> Sales)

[rahulpawarb]

Hello Snehasis,

Trust that you are doing great!

To fulfill your requirement follow below given steps:

1. Create new chart as Pivot table (Title - Net Sales).

2. Add Region & Sub-Region dimensions from Available Fields/Groups to Used Dimensions.

3. Next, add Net Sales expression i.e. Sum(Sales)

4. Post that add, % Sales Contribution/Region expression definition as below:

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

    Sum(Sales) - will give total sales for that Region and Sub-Region

    Sum(TOTAL <Region> Sales) - will generate Region level Sales total

5. For % Sales Contribution/Region show in Percent (%) settings under Number tab.

Please refer sample application attached herewith.

Regards!

Rahul

[ashish_2511]

Thanks Deva! In Fact, I was also using the same expression. Do not know why it gets null values at some places.

Vineeth's solution works perfectly though.

[ashish_2511]

Thanks a lot Vineeth, this works just fine. I had totally forgot about Total..