# Margin percentage as row in PowerPivot



## fa909 (Oct 9, 2017)

I am building an Income Statement using PowerPivot. I have calculated rows for Gross Margin and Net Income. I want to add a row for Gross Margin %. i.e.:

I currently have as a running total: Revenue - Costs = Gross Margin
I want to add the following Percentage as a row: Gross Margin % = Gross Margin / Revenue. 
Your help is much appreciated.
Best
Farah


----------



## Matt Allington (Oct 10, 2017)

I recommend you watch this excellent video from Derek Rickard https://www.youtube.com/watch?v=ojHZkWkEY7Q/


----------



## fa909 (Oct 10, 2017)

Matt Allington said:


> I recommend you watch this excellent video from Derek Rickard https://www.youtube.com/watch?v=ojHZkWkEY7Q/



Thank you Matt. I watched the video and it was very helpful in building an Income Statement. Does it doesn't go through how to calculate gross margin as a % of sales, it only goes through Gross Margin$ in absolute terms.


----------



## Matt Allington (Oct 11, 2017)

Well Margin as a % of gross sales is simply 

= divide([Gross Margin],[Total Sales])

of course [Total Sales] has to return the correct value for this to work regardless of where it is in a pivot table.  I suggest setting up your pivot table first and then add [Gross Margin] and [Total Sales] to the pivot.  For the % to work, the [Gross Margin] must change for each row in the pivot, but the [Total Sales] must always return the [Total Sales].  it will likely be something like this

=CALCULATE(sum(table[value]),table[Category]="Sales") or something like that.


----------



## fa909 (Oct 12, 2017)

That worked!! Thank you Matt!


----------

