I have a PowerPivot table similar to the following (obviously thousands more lines):
<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:
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?
P&L Category | Amount | Something else I may Pivot on |
Sales | 100 | A |
Sales | 100 | A |
Discounts | 20 | A |
Discounts | 30 | A |
Returns | 10 | C |
Sales | 200 | C |
<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: