5 Replies Latest reply: Oct 20, 2015 12:05 PM by Josh Campbell

# Using set analysis to calculate percentage

Greetings,

I am using the following dimension fields in a straight chart: project number, project name, allocation code, project type. I have expressions rowno() ,SUM(Amount) for 2011, 2012, 2013, and Year To Date totals. I need to create a percentage. There is an extra field, "business unit", required to create this percentage, but it is not used in the dimensions. The formula for the percentage should be " project total by business unit divided by the project total". I have tried using expressions like sum({\$<business unit = business unit>}Amount)/SUM({\$<project=project>}Amount) which gives me 0s down the board. I have tried aggr(SUM({\$}amount),businessunit)/aggr(SUM({\$}amount),project) * .01. This produces dahes "-" down the board. I am at a standstill. I am sure somebody has done this before but I am not choosing the correct search criteria for this site. Any assistance is greatly appreciated.

Thanks,

Frank

• ###### Re: Using set analysis to calculate percentage
The formula for the percentage should be " project total by business unit divided by the project total"

That sounds like sum(total <[business unit]> amount)/sum(total amount)

If not, please post a sample document with some data.

• ###### Re: Using set analysis to calculate percentage

I think I understand what you're after. Project and  Business Unit needs to be Dimensions and the following your expression.

If you share your data / application I can confirm / create an example.

• ###### Re: Using set analysis to calculate percentage

Gysbert, Shane,

Thanks for the quick reply. I had to pare down my file to fit within company e-mail attachment standards. I have it attached. After this question is answered I will be able to post another question concerning this file.

Thanks,

Frank

• ###### Re: Re: Using set analysis to calculate percentage

Please take a look at the pivot table and straight table I have created as examples on Sheet1. I think this is what you are after, if not please elaborate.

• ###### Re: Re: Using set analysis to calculate percentage

Another option using Set Analysis is the following:

Num(Sum({<[Year]={'2015'}>} [Sales]) - Sum({<[Year]={'2014'}>} [Sales])) / Sum({<[Year]={'2014'}>} [Sales])