6 Replies Latest reply on May 30, 2017 5:23 AM by Khalid Baker

# How can I calculate % of total sales based on Category? (Juice 30%, Energy 70% etc.)

Hey Everyone;

Attached is the file.

I would like to calculate a percentage of total sales per category. For Example, at the continent level, it shows Juice 30% and Energy 70%, but I would like to have at regional level than at country level potentially. For example, MENA would be 80% Energy & 20% Juice, but at the continent level it is 70% Energy and 30% Juice.

Kindly help me with this calculation.

Kind Regards;

Khalid Baker

• ###### 1. Re: How can I calculate % of total sales based on Category? (Juice 30%, Energy 70% etc.)

Khalid,

if so, please see attached, otherwise correct me where I am off. Thank you

Sherzod

• ###### 2. Re: How can I calculate % of total sales based on Category? (Juice 30%, Energy 70% etc.)

Hey Sherzod;

This works, but in my case I would like to include it in the details for the chart.

For example:

If we are looking at a single bar in a chart for the continent. It would say 30% Juice & 70% Energy. When you drilldown to region (Creating a Hierarchy Continent-Region & Category-Brand), than MENA 80% Energy & 20% Juice, whilst India for example is 40% Juice and 60% Energy.

Like do you I have to create a formula each part of the continent hierarchy ? I have multiple dimensions there in the actual data set.

Appreciate all the help mate!

Kind Regards;

Khalid Baker

• ###### 3. Re: How can I calculate % of total sales based on Category? (Juice 30%, Energy 70% etc.)

Khalid,

Sherzod

• ###### 4. Re: How can I calculate % of total sales based on Category? (Juice 30%, Energy 70% etc.)

Essentially, I had to add more dimensions to the Regional Sales Formula for it to work.

Thanks Sherzod!

Maybe you can help me with the following:

Kind Regards;

Khalid Baker

• ###### 5. Re: How can I calculate % of total sales based on Category? (Juice 30%, Energy 70% etc.)

Hello Sherzodbek,

This logic of calculating the percent of total appears confusing to me.

Why [Region Sales] / [Total Sales] don't work in this case?

Thanks,

Esther

• ###### 6. Re: How can I calculate % of total sales based on Category? (Juice 30%, Energy 70% etc.)

Hey Esther;

The logic with the table calculation % of total should not confuse.

When you select the table calculation make sure you do it by category, which would then show the correct indication whether it is at continent level or regional level.

Hope this makes it look easier! You can see the difference from continent to regions. Also, make sure to select 'Edit Table Calculation...' and select Category under specific dimensions.

Kind Regards;

Khalid B

1 of 1 people found this helpful