Custom Calculations for Invoice & Returns using PowerPivot DAX formula

Standard

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

Problem Description

Here’s the sample data:

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

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:
 Power Pivot Excel DAX Invoice

Step 3

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

Conclusion

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.