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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Threads
1,095,690
Messages
5,445,977
Members
405,373
Latest member
Marlene Mayo

This Week's Hot Topics

Top