Results 1 to 1 of 1

Thread: Trying to get a Net Sales rollup in PowerPivot
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Trying to get a Net Sales rollup in PowerPivot

    I have a PowerPivot table similar to the following (obviously thousands more lines):
    P&L Category Amount Something else I may Pivot on
    Sales 100 A
    Sales 100 A
    Discounts 20 A
    Discounts 30 A
    Returns 10 C
    Sales 200 C

    I want to get a summary of Net Sales in a Pivot, which calculates to Sales - Discounts - Returns.

    Amount is also put into a PowerPivot Calculated Field:= SUM([Total Sales])

    I've tried to do something like this, but not getting the write results:
    =CALCULATE([Total Sales],AccountNameMatch[P&L Category]="Sales")
    -CALCULATE([Total Sales],AccountNameMatch[P&L Category]="Discounts")
    -CALCULATE([Total Sales],AccountNameMatch[P&L Category]="Returns")

    I don't quite get the result I'd like, or this is maybe too clunky of a way to do this. I also tried with a SUMX but that didn't work. I also will likely Pivot on A or C above to check (that would represent, say, customers.)

    I bet this is easy to the Daily PowerPivot user but I can't quite get it right. Any advice here?
    Last edited by Vaslo; May 15th, 2019 at 02:22 PM. Reason: Added info about the calc field

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts