6 Replies Latest reply on May 12, 2018 6:27 AM by Zhouyi Zhang

# Find % of total of dimension value

I am trying to find the percent of total of all dimension values against one dimension total without table calculations or filters I need to be able to use this in other calculated fields.

So if I do this to get total sales for one segment:

IF [Segment] = 'corporate'

THEN [Sales]

ELSE NULL

END

I want to get the percent of the two other segment to that total. I have included a sample workbook.

• ###### 1. Re: Find % of total of dimension value

Hi, LL

Please find my solution attached.

below is the screenshot of result and calculation

Hope this helps

ZZ

• ###### 2. Re: Find % of total of dimension value

unfortunately this is just percent of total sales. I want to find the percent of consumer and home office of corporate sales. with out table calculations or the use of filters.

• ###### 3. Re: Find % of total of dimension value

Can you show the formula how you calculate with the numbers in your sample?

ZZ

• ###### 4. Re: Find % of total of dimension value

Hi L,

Based on your original post the calculation is following:

{ SUM(IF [Segment] = 'Corporate' OR [Segment]='Consumer' THEN [Sales] END) }/ {SUM([Sales])}

So, we give a condition for values which should be summed and divide it by the total figure.

Please find workbook attached.

Trust this helps.

D

• ###### 5. Re: Find % of total of dimension value

Hi,

Do you want to divide sales from corporate by sum of sales from the other two segments?

[Corporate Sales]

IF [Segment] = 'Corporate' THEN [Sales] END

[Other Segment Sales]

IF [Segment] <> 'Corporate' THEN [Sales] END

You can also calculate other segment sales by explicitly defining the segments to include in the total.

[Other Segment Sales]

IF [Segment] = 'Consumer' OR [Segment] = 'Home Office' THEN [Sales] END

The next step is to calculate the percentage. I'm not entirely sure what you want but either of the options below may be useful for you.

[Option 1]

{SUM([Corporate Sales])}/{SUM([Other Segment Sales])}

[Option 2]

{SUM([Other Segment Sales])}/{SUM([Corporate Sales])}

Option 1 and Option 2 calculations are wrapped in braces to fix them for the entire dataset. They won't be affected by certain filters. Remove the braces if you want all filter actions to affect the percentage calculation.

Hope this helps.

Ossai

• ###### 6. Re: Find % of total of dimension value

Or something like this?

ZZ