# POWERPIVOT CALCULATION IDEAS

### Question

• 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

Monday, July 08, 2013 7:38 PM

• 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:

Invoiced-Returned:=[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 )

Monday, July 08, 2013 8:16 PM

### All replies

• Is this your desired output?

Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

Monday, July 08, 2013 7:53 PM
• YES!!!! but I can't see the images you put.

Can you please explain to me what you did?

Monday, July 08, 2013 8:03 PM
• 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:

Invoiced-Returned:=[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 )

Monday, July 08, 2013 8:16 PM
• THANK YOU SO MUCH!!! IT WORKED :)
Monday, July 08, 2013 8:29 PM