none
Power Pivot Help!! RRS feed

  • 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.

    Thursday, July 18, 2019 3:27 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

    Thursday, July 18, 2019 3:59 PM
  • 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.

    Thursday, July 18, 2019 4:13 PM
  • 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

    Friday, July 19, 2019 3:43 AM
  • Thanks I think I got it to work. Thank you for your help!!
    Wednesday, July 24, 2019 5:24 PM