Power Pivot Help!!

Question

Power Pivot help!!

Hello. I’m new to this tool. Need some guidance for the following. Below are the data inserted to power pivot.

Date	Item #	Category	Shipped	standard cost
6/1/2018	A896	A	1	$2.50
6/1/2018	B276	B	1	$1.00
6/1/2018	A896	A	2	$2.50
6/1/2018	C409	C	3	$1.45
6/2/2018	A896	A	1	$2.50
6/3/2018	Z109	Z	1	$3.00
6/3/2018	A896	A	2	$2.50
6/3/2018	D009	D	1	$1.00
7/3/2018	B276	B	1	$1.00
7/3/2018	Z109	Z	3	$3.00
7/4/2018	Z109	Z	1	$3.00
7/4/2018	A896	A	2	$2.50
7/5/2018	A896	A	1	$2.50
7/5/2018	B276	B	3	$1.00
7/5/2018	D009	D	4	$1.00
7/5/2018	C409	C	1	$1.45
7/9/2018	A896	A	2	$2.50
7/9/2018	D009	D	4	$1.00
7/9/2018	B276	B	2	$1.00
7/10/2018	A896	A	2	$2.50
7/10/2018	D009	D	1	$1.00
7/10/2018	C409	C	1	$1.45
7/10/2018	Z109	Z	1	$3.00
8/2/2018	B276	B	2	$1.00
8/2/2018	D009	D	1	$1.00
8/2/2018	C409	C	3	$1.45

How do I go about creating a pivot table that display like the table below. Where each category value in month/year is calculated by the average shipped over the past 6 months and then multiply it by its standard cost.

Category
Month/Year	A	B	C	D	Z
Jun-2018	$28.33	$7.33	$7.98	$10.17	$15.50
Jul-2018
Aug-2018

Any suggestion would be greatly appreciated.

Answer 1

Hi Mashimoron,

Can you please check if following values are correct. As per your requirement  A value should get $15

Category
Month/Year	A	B	C	D	Z
Jun-2018	$28.33	$7.33	$7.98	$10.17	$15.50

---

Thanks

Srinivasa Rao G, MCSE(Business Intelligence) Blog: TechTalksPro

Please mark as answer if my post is helped to solve your problem
and vote as helpful if it helped so that forum users can benefit

Answer 2

Thank you Srinvasa. 

Let me explain it a little bit more detail. Let’s take category A as an example. So in Month of Aug-2018. I would take the average of the total shipped from Aug, Jul, Jun, May, Apr, Mar and times the standard cost for that particular item. So July 2018 would be the average of July, June, May, April, March, February. So basically, past 6 months during that month. (Note: There could be more item under Category A with different standard cost).

Hope I didn’t confuse anyone yet.

Answer 3

Excel 365 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
Average over last 6 months.
Multiple items per category.
Cost per item fixed.
http://www.mediafire.com/file/hconfovkg3bndkl/07_18_19b.xlsx/file
http://www.mediafire.com/file/71083gh9nfwm0zg/07_18_19b.pdf/file

Answer 4

Thanks I think I got it to work. Thank you for your help!!