Custom Calculations for Invoice & Returns Using a PowerPivot DAX Formula

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.