3 Replies Latest reply on Feb 23, 2018 2:47 AM by Yuriy Fal

# Percentage of Sales at view level (Op income / Net income / Gross Margin)

Hello,

I'm trying to figure out how to show an aggregate calculation that will allow to have a calculated field that will show total sales in EVERY ROW within the Net income dimension. I'm building an income statement, and want to show each line as a percentage of total sales. I want the data set to be dynamic, meaning that there are multiple companies and years within the data set, and so I would like the %'s to update when filtering through data.

I tried using an if then statement

IF [* P&L - L4 Sales / COGS]=="Net sales" THEN[Amount (LC)]END

and then a non-aggregate calculated field

{sum([Net sales])}

The second formula gave me a total on every row, but it was totalling ALL company codes.

Here's an example of how the income statement is being built. So for example I would want to see Marketing as a \$ of total Net Sales.

Any info you can provide would be great.

Santos

• ###### 1. Re: Percentage of Sales at view level (Op income / Net income / Gross Margin)

Hi Santos,

If you'd like to have a distinct value of the denominator

for each [# Company Code], you could add it to the calc:

{FIXED [# Company Code] : SUM( [Net sales] ) }

Same with the other Dimensions as well.

Besides, if you have to restrict the scope

of your FIXED LOD calc(s) by some Dimensions,

you should add the dimension pills to Context.

For example, the Fiscal Year (or the date range)

could be a good candidate for a Context Filter.

Yours,

Yuri

• ###### 2. Re: Percentage of Sales at view level (Op income / Net income / Gross Margin)

Yes it worked! Thank you!

• ###### 3. Re: Percentage of Sales at view level (Op income / Net income / Gross Margin)

Santos, you're welcome.