Trying to get a Net Sales rollup in PowerPivot

Vaslo

Board Regular
Joined
Jun 3, 2009
Messages
156
I have a PowerPivot table similar to the following (obviously thousands more lines):
P&L CategoryAmountSomething else I may Pivot on
Sales100A
Sales100A
Discounts20A
Discounts30A
Returns10C
Sales200C

<tbody>
</tbody>

I want to get a summary of Net Sales in a Pivot, which calculates to Sales - Discounts - Returns.

Amount is also put into a PowerPivot Calculated Field:= SUM([Total Sales])


I've tried to do something like this, but not getting the write results:
Code:
=CALCULATE([Total Sales],AccountNameMatch[P&L Category]="Sales")
-CALCULATE([Total Sales],AccountNameMatch[P&L Category]="Discounts")
-CALCULATE([Total Sales],AccountNameMatch[P&L Category]="Returns")

I don't quite get the result I'd like, or this is maybe too clunky of a way to do this. I also tried with a SUMX but that didn't work. I also will likely Pivot on A or C above to check (that would represent, say, customers.)

I bet this is easy to the Daily PowerPivot user but I can't quite get it right. Any advice here?
 
Last edited:

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Watch MrExcel Video

Forum statistics

Threads
1,099,134
Messages
5,466,866
Members
406,506
Latest member
Patb3

This Week's Hot Topics

Top