Using set analysis to calculate percentage

[#1]

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

[Gysbert_Wassenaar]

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.

[shane_spencer]

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

num(Sum(Amount)/Sum(total <[business unit]> Amount),'#0.0%')

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

[#4]

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

[shane_spencer · Accepted solution]

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.

[jcampbell]

Another option using Set Analysis is the following:

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