Margin percentage as row in PowerPivot

[fa909]

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]

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

 

[fa909]

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]

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]

That worked!! Thank you Matt!