This is from the thread 
in the SQL Server PowerPivot for Excel forum.
Problem Description
Here’s the sample data:
| 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 |
What the user wanted was an output like this:
Without PowerPivo this is how the user was doing it:
“Create 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.”
Solution
Let’s see how DAX formula in PowerPivot can help the user so that it eliminates the “manual” calculation.
So Here are the steps:
Step 1
Create two calculated measures:
Invoiced:=CALCULATE(SUM([SALES]),TABLENAME[STATUS]=”INVOICE”)
Returned:=CALCULATE(sum(DATA[SALES]),TABLENAME[STATUS]=”RETURN”)
Step 2
Create one more calculated measure:
Invoiced-Returned:=[Invoiced]-[Returned]
Now from the usability standpoint, Hide measures created in step 1
Here’s the screenshot of the PowerPivot Model:
Step 3
Let’s view this using PivotTables:
Conclusion
In this post, we saw how to create custom calculation to handle invoices and returns using PowerPivot DAX formula’s.