# Calculated Column or New Measure

This is a discussion on Calculated Column or New Measure within the PowerPivot Questions forums, part of the Question Forums category; Hello, I receive sales reports on daily basis and I need to analyze the received data. It's mainly 2 reports ...

1. ## Calculated Column or New Measure

Hello,

I receive sales reports on daily basis and I need to analyze the received data.

It's mainly 2 reports one of them is the sales and the other is the sales returns.

Both of them have exactly the same structure which looks like this:

Excel 2010
D E F G H
1 Month Customer Name Product Name Sales Quantity Sales Bonus Quantity
2 Jan Customer 1 Product 1 1
3 Jan Customer 2 Product 2 99 45
4 Feb Customer 3 Product 3 3
5 Feb Customer 4 Product 4 22
6 Mar Customer 5 Product 5 2
Sheet1
I need to get the final sales which is:
sales quantity - sales returns
and the same for the bonus quantities.

I created a new file with connections to the 2 files that I receive and tried adding a new measure and adding a new calculated column in the powerpivot but that results were wrong.

Any help will be appreciated.

2. ## Re: Calculated Column or New Measure

The problem is solved, but I can't calculate the bonus percentage through a new measure, the value is wrong.
I hope somebody can help.

3. ## Re: Calculated Column or New Measure

I'm guessing you want a table with products as rows and months as columns or something similar. You then want the sum of sales quantity for that month for that product - sum sales returns for that same month and that same product. Same thing for bonuses so 2 outputs per input combination. Can you give me the measure you used?

Mike

4. ## Re: Calculated Column or New Measure

Thank you Mike for your response.

I managed to get the net sales by deducting the returns from the sales for each customer every month through making multiple relations in the powerpivot main interface.

The problem now is to get the percentage of bonus for each sales process.

The weird thing is that in the generated pivot table I can see the correct numbers of the sales and the bonus quantities, for some records the bonus percentage is calculated correctly, but for others it's not.

The measure I used is this:
=if(SUMX(Invoices,Invoices[FinalQty]),SUMX(Invoices,Invoices[FinalBonus])/SUMX(Invoices,Invoices[FinalQty]),blank())

Taking into consideration the following:
FinalQty is a measure I made calculating the (sales - returns) the values are correct for each record
FinalBonus is a measure I made calculating the (sales bonus - returns bonus) the values are also correct

The results are like this, you can see at column X the correct values and the calculated Bonus% (column V) in the pivot table is wrong:

Excel 2010
TUVWX
2Total QtyTotal BonusTotal Bonus%
3
4
51,43539227.78%27.32%
61,97153827.30%27.30%
750220245.11%40.24%

Report

5. ## Re: Calculated Column or New Measure

what are the rows for this table? Is it by product and/or customer?

Is the column "Total Qty" the measure FinalQty or some other measure suming the FinalQty numbers?

How big is the spreadsheet as you could try emailing it if not too big?

6. ## Re: Calculated Column or New Measure

Hello Mike,
The rows have both products and custmers.

"Total Qty" column is the grand total of "FinalQty".

The file is less than 650 KB and (230 KB after compression) but I can't find an option in the forum to add attachments.

7. ## Re: Calculated Column or New Measure

The problem is solved, Mike (masplin) made the solution and I just would like to share the idea with anybody who might face it later.

For percentage calculation the SUMX() function didn't work, simply making a very plain division worked nicely.

The erroneous measure was:
=if(SUMX(Invoices,Invoices[FinalQty]),SUMX(Invoices,Invoices[FinalBonus])/SUMX(Invoices,Invoices[FinalQty]),blank())

The corrected measure based on Mike's solution looks like this:
=if(Invoices[FinalQty]<>0, Invoices[FinalBonus]/Invoices[FinalQty],Blank())

Thank you Mike for your effort and time.

#### Posting Permissions

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