Answered by:
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.
Answers
-
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
- Proposed as answer by Olaf HelperMVP, Moderator Thursday, July 25, 2019 6:09 AM
- Marked as answer by Times DecidesEditor Sunday, October 13, 2019 2:42 PM
All replies
-
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 -
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.
-
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
- Proposed as answer by Olaf HelperMVP, Moderator Thursday, July 25, 2019 6:09 AM
- Marked as answer by Times DecidesEditor Sunday, October 13, 2019 2:42 PM
-