Custom Calculations for Invoice & Returns Using a PowerPivot DAX Formula

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.

Leave a Comment
  • Please add 7 and 8 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • Ed Price - MSFT edited Revision 7. Comment: Title casing, tags. Great article!

  • Naomi  N edited Revision 4. Comment: Minor edit

  • Naomi  N edited Original. Comment: Added TOC and some grammar corrections

Page 1 of 1 (3 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
Page 1 of 1 (4 items)