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:
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.