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