Resources For IT Professionals
Post an article
Wikis - 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
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  Custom Calculations for Invoice & Returns Using a PowerPivot DAX Formula

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.

Table of Contents



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.

, , , , , , , , , , , , , [Edit tags]
Leave a Comment
  • Please add 6 and 8 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 6 Aug 2013 6:49 PM

    Ed Price - MSFT edited Revision 7. Comment: Title casing, tags. Great article!

  • 10 Jul 2013 7:03 PM

    Naomi  N edited Revision 4. Comment: Minor edit

  • 8 Jul 2013 8:26 PM

    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)