Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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.
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
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.
Another option using Set Analysis is the following:
Num(Sum({<[Year]={'2015'}>} [Sales]) - Sum({<[Year]={'2014'}>} [Sales])) / Sum({<[Year]={'2014'}>} [Sales])