Creating % of net sales in data/pivot

[mwaltercpa]

I am working with data in PowerPivot that includes period, location, line description, and $ amt. I also want to calculate a % of net sales (net=Sales less returns) for each line below, with respect to the period# and Store location:

Period Store Desc Amt VVV
1 A Sales $120 120%
1 A Returns $<20> -20%
1 A Expense $<10> -10%

1 B Sales $130 108%
1 B Returns $<10> -8%
1 B Expense $<12> -10%

2 A Sales $120 120%
2 A Returns $<20> -20%
2 A Expense $<10> -10%

Any help would be appreciated!

 

[scottsen]

It will be something like this:

[NetSales] := SUM(SalesTable[Sales]) - SUM(SalesTable[Returns])
[NetSales - All Period and Store] := CALCULATE([NetSales], ALL(DatesTable[Period], StoreTable[StoreId]))
[% Sales] = DIVIDE([NetSales], [NetSales - All Period and Store])

 

[mwaltercpa]

Thanks! That helped me understand how to calculate new fields to work from, like net sales.
I created a sample financial report and got this to work by adding these three CALCULATE formulas.
Is there any way to combine formula #1 and #2? When I tried to add two CALCULATE formulas together, or use an OR command, it gave me an error. So I split out Sales and Sales Returns, and then just added them in the % calc.
To be really crazy, could I have combined all three of these formulas?? - Thanks again for your help!

#1: SALESTOTAL:=CALCULATE(SUM('SAMPLE FS'[AMT]),'SAMPLE FS'[DESC]="SALES")
#2: SALESRETTOTAL:=CALCULATE(SUM('SAMPLE FS'[AMT]),'SAMPLE FS'[DESC]="SALES RET")
#3: %OFNET:=CALCULATE(SUM('SAMPLE FS'[AMT])/([SALESTOTAL]+[SALESRETTOTAL]))

 

[scottsen]

I'm not sure what you mean by "combine". Do you mean the SUM([AMT]) where DESC = "Sales" or DESC = "Sales RET" ?

Your #3 reads kinda weird to me. That would be the sum(Sales+Ret+Expenses) / sum(Sales+Ret) ? I dunno, maybe you want (but you also are not passing any intersting filters to CALCULATE so it is probably not needed?)

The reality is I would have 3 "base measures" [Total Sales], [Total Returns] and [Total Expenses], and base any other measures off those guys.

 

[mwaltercpa]

Thanks again Scott!

Yes I am interested in if I can write one expression that sums up the total of "SALES" AND "SALES RET" (my sales return is a negative number in my sample report, so adding them would give me a net amt)

If the cleanest way to do this is to sum up each field individually, then work from those new figures, then thats great. It actually is working very well in my sample. I just don't want to be doing this for the next 10 years then hear someone say "you know there is an easier way of doing that right??" LOL

Thanks again! If you have any good book or video suggestions for me to learn from I would appreciate it!

 

[scottsen]

There are a bunch of ways to do this, probably just personal preference at this point.

I know that I would write it as a [Total Sales] and [Total Returns] and use those to create a [Net Sales], but clearly you could also do it "directly":

[NetSales] := CALCULATE(SUM('SAMPLE FS'[AMT]),'SAMPLE FS'[DESC]="SALES RET" || 'SAMPLE FS'[DESC]="SALES")

I think Rob (PowerPivotPro) has the most practical/approachable book: http://goo.gl/FgmySf

 

[mwaltercpa]

Great advice, thank you! I read the reviews on that book and that sounds like the book for me... Strong Excel, new to PP/DAX. I've picked up things from your comments, and going to YouTube. I am now used to calculating and working w those totals as seperate measures, but also like knowing I can combine my conditions. I thought plain Excel was powerful and flexible.. PP takes that to a whole new level. I'm getting 64bit very soon and hope to take years of data and create dashboards from PP. Thanks again for getting me going

Mark