Answered by:
POWERPIVOT CALCULATION IDEAS

Below is the simplified data that I am working on.
In status column, there are INVOICE and RETURN data, by weekly, by customer and sales.
This table links to another table to pull sales rep name. Therefore I am using PowerPivot.
What I would like to do is calculate invoiced amount  returned amount by weekly in one pivot table.
Right now, I am creating one pivot table filtered by INVOICE (WEEK in Columns, CUSTOMER in Rows) and second table filtered by RETURN (WEEK in Columns, CUSTOMER in Rows). Then manually calculate INVOICED pivot  RETURN pivot.
Is there any way to calculate this in one powerpivot table?
EXAMPLE, W01 has one invoice and one return. so, W01 sales number in the pivot should say $0
STATUS WEEK SALES CUSTOMER INVOICE W01 $150.00 A RETURN W02 $120.00 B INVOICE W02 $120.00 B INVOICE W02 $130.00 C INVOICE W02 $150.00 D INVOICE W03 $130.00 E INVOICE W03 $120.00 F RETURN W01 $150.00 A INVOICE W04 $100.00 G INVOICE W05 $150.00 H RETURN W03 $130.00 E RETURN W02 $120.00 B RETURN W06 $100.00 I INVOICE W06 $100.00 I RETURN W05 $150.00 H
Question
Answers

I just wanted to make sure that it's what you were looking for and so I didn't include the steps. But since I know it's what you are looking for, here are the steps:
1. Create two calculated measures:
Invoiced:=CALCULATE(SUM([SALES]),DATA[STATUS]="INVOICE")
Returned:=CALCULATE(sum(DATA[SALES]),DATA[STATUS]="RETURN")
Note: the name of my table is DATA. you'll need to add your table name.
2. Now create one more calculated measure:
InvoicedReturned:=[Invoiced][Returned]
(From usability standpoint, you can hide measures created in step 1).
Now view the Model via Pivot Tables and you should be able to see what I posted earlier:
And here's the PowerPivot Model screenshot:
Paras Doshi (Blog: ParasDoshi.com  Twitter: @Paras_Doshi )
 Proposed as answer by Paras DoshiEditor Monday, July 08, 2013 8:19 PM
 Marked as answer by YJB5151 Monday, July 08, 2013 8:30 PM
 Edited by Paras DoshiEditor Monday, July 08, 2013 9:00 PM added note
All replies

 Edited by Paras DoshiEditor Monday, July 08, 2013 7:54 PM signature


I just wanted to make sure that it's what you were looking for and so I didn't include the steps. But since I know it's what you are looking for, here are the steps:
1. Create two calculated measures:
Invoiced:=CALCULATE(SUM([SALES]),DATA[STATUS]="INVOICE")
Returned:=CALCULATE(sum(DATA[SALES]),DATA[STATUS]="RETURN")
Note: the name of my table is DATA. you'll need to add your table name.
2. Now create one more calculated measure:
InvoicedReturned:=[Invoiced][Returned]
(From usability standpoint, you can hide measures created in step 1).
Now view the Model via Pivot Tables and you should be able to see what I posted earlier:
And here's the PowerPivot Model screenshot:
Paras Doshi (Blog: ParasDoshi.com  Twitter: @Paras_Doshi )
 Proposed as answer by Paras DoshiEditor Monday, July 08, 2013 8:19 PM
 Marked as answer by YJB5151 Monday, July 08, 2013 8:30 PM
 Edited by Paras DoshiEditor Monday, July 08, 2013 9:00 PM added note
