Calculated Column or New Measure

[DrDaeMoN]

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
Month	Customer Name	Product Name	Sales Quantity	Sales Bonus Quantity
Jan	Customer 1	Product 1
Jan	Customer 2	Product 2
Feb	Customer 3	Product 3
Feb	Customer 4	Product 4
Mar	Customer 5	Product 5

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: center"]2
[/TD]

[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3
[/TD]

[TD="align: right"]99
[/TD]
[TD="align: right"]45
[/TD]

[TD="align: center"]4
[/TD]

[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]

[TD="align: right"]22
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]

[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]

</tbody>

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.

 

[DrDaeMoN]

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.

 

[masplin]

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

 

[DrDaeMoN]

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
	T	U	V	W	X
2	Total Qty	Total Bonus	Total Bonus%
3
4
5	1,435	392	27.78%		27.32%
6	1,971	538	27.30%		27.30%
7	502	202	45.11%		40.24%
Report

 

[masplin]

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?

 

[DrDaeMoN]

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.

 

[DrDaeMoN]

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.