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

What do you think? Leave a comment below.