Resources For IT Professionals
Page Details
  • First published by (eMicrosoft Partne)
  • When: 8 Jul 2013 2:18 PM
  • Last revision by (aMicrosof)
  • When: 6 Aug 2013 6:49 PM
  • Revisions: 9
  • Comments: 4
Options

Original

Wiki  >  TechNet Articles  >  Custom Calculations for Invoice & Returns Using a PowerPivot DAX Formula  >  Original
Custom Calculations for Invoice & Returns Using a PowerPivot DAX Formula
You are currently reviewing an older revision of this page.
Go to current version
This is from the thread in the SQL Server PowerPivot for Excel forum.
Problem:

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 PowerPivot, Here's how User was doing it:
"creating 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.
Revert to this revision