3 Replies Latest reply on Mar 17, 2016 12:54 PM by Andy D

# Percent of total but based on the individual columns and not the total for all (ie not add up to 100%)

Hi.  I'm trying to do something similar to what I would do with sumifs in Excel.

I want to sum up values within a range and have that as a % of the columns for each.

1)  I can't seem to do the value range because I get an error about mixing aggregate / non aggregate.  For example, I would want  do something like...

if [value] >= 200 and [value] <= 400 then sum[value] end

he only way I can seem to get that to work is to just add a filter that gives me the range I want.  Is there another way of handling this?

2) I then want to take that value and divide by total for each column so that I have the % of each columns total.  Percent of Total calculation will distribute among the columns so adds up to 100% but I don't want it to add to 100.

Anyone able to help?

• ###### 1. Re: Percent of total but based on the individual columns and not the total for all (ie not add up to 100%)

Hi Andy!

Are you able to attach some sample data? You either you need to aggregate both sides of the IF/THEN expression or don't aggregate any.

So, this would be a row-level calculation: if [value] >= 200 and [value] <= 400 then [value] end

And this would be an aggregate: if sum([value]) >= 200 and sum([value]) <= 400 then sum([value]) end

I do not understand your 2nd question, for which sample data would help.

Pooja.

• ###### 2. Re: Percent of total but based on the individual columns and not the total for all (ie not add up to 100%)

Hi Pooja.  Thanks!  I forget that I don't need to do the sum and let that

aggregation occur when I put the field on rows.

So that works - what I want ultimately is for the percent of total to be

based on the total of that specific category.  The percent of total quick

calc distributes the %s amongst the categories and adds up to 100%. I don't

want that.  What I want is the % to be based on the value of gotten from

the range over the total of that category.  So if a bar chart, the % would

only be based on values on the one bar.  I don't want it to be the total of

all bars

• ###### 3. Re: Percent of total but based on the individual columns and not the total for all (ie not add up to 100%)

I got it now - I had to sum the if statement then divide that by total(the same field)

So something like...

Sum( if  >= 200 and  <=2000 then  end) / total(sum())