none
POWERPIVOT CALCULATION IDEAS

    Question

  • Below is the simplified data that I am working on.

    In status column, there are INVOICE and RETURN data, by weekly, by customer and sales.

    This table links to another table to pull sales rep name. Therefore I am using PowerPivot.

    What I would like to do is calculate invoiced amount - returned amount by weekly in one pivot table.

    Right now, I am 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.

    Is there any way to calculate this in one powerpivot table?

    EXAMPLE, W01 has one invoice and one return. so, W01 sales number in the pivot should say $0

    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

    Monday, July 08, 2013 7:38 PM

Answers

  • I just wanted to make sure that it's what you were looking for and so I didn't include the steps. But since I know it's what you are looking for, here are the steps:


    1. Create two calculated measures:

    Invoiced:=CALCULATE(SUM([SALES]),DATA[STATUS]="INVOICE")

    Returned:=CALCULATE(sum(DATA[SALES]),DATA[STATUS]="RETURN")

    Note: the name of my table is DATA. you'll need to add your table name.

    2. Now create one more calculated measure:

    Invoiced-Returned:=[Invoiced]-[Returned]

    (From usability standpoint, you can hide measures created in step 1).

    Now view the Model via Pivot Tables and you should be able to see what I posted earlier:

    And here's the PowerPivot Model screenshot: 


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )




    Monday, July 08, 2013 8:16 PM
    Answerer

All replies

  • Is this your desired output?


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

    Monday, July 08, 2013 7:53 PM
    Answerer
  • YES!!!! but I can't see the images you put.

    Can you please explain to me what you did?

    Monday, July 08, 2013 8:03 PM
  • I just wanted to make sure that it's what you were looking for and so I didn't include the steps. But since I know it's what you are looking for, here are the steps:


    1. Create two calculated measures:

    Invoiced:=CALCULATE(SUM([SALES]),DATA[STATUS]="INVOICE")

    Returned:=CALCULATE(sum(DATA[SALES]),DATA[STATUS]="RETURN")

    Note: the name of my table is DATA. you'll need to add your table name.

    2. Now create one more calculated measure:

    Invoiced-Returned:=[Invoiced]-[Returned]

    (From usability standpoint, you can hide measures created in step 1).

    Now view the Model via Pivot Tables and you should be able to see what I posted earlier:

    And here's the PowerPivot Model screenshot: 


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )




    Monday, July 08, 2013 8:16 PM
    Answerer
  • THANK YOU SO MUCH!!! IT WORKED :)
    Monday, July 08, 2013 8:29 PM