Custom Calculations for Invoice & Returns using PowerPivot DAX formula


This is from the thread in the SQL Server PowerPivot for Excel forum.

Problem Description

Here’s the sample data:

INVOICEW01$150.00  A
RETURNW02$120.00  B
INVOICEW02$120.00  B
INVOICEW02$130.00  C
INVOICEW02$150.00  D
INVOICEW03$130.00  E
INVOICEW03$120.00  F
RETURNW01$150.00  A
INVOICEW04$100.00  G
INVOICEW05$150.00  H
RETURNW03$130.00  E
RETURNW02$120.00  B
RETURNW06$100.00  I
INVOICEW06$100.00  I
RETURNW05$150.00  H

What the user wanted was an output like this:
 Excel Invoices and returns calculations

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.”


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:




Step 2

Create one more calculated measure:

Now from the usability standpoint, Hide measures created in step 1

Here’s the screenshot of the PowerPivot Model:
 Power Pivot Excel DAX Invoice

Step 3

Let’s view this using PivotTables:
 Excel Power Pivot excel and DAX


In this post, we saw how to create custom calculation to handle invoices and returns using PowerPivot DAX formula’s.

Paras Doshi