Results 1 to 7 of 7

Thread: Powepivot Calculation

  1. #1

    Powepivot Calculation



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I am trying to do a days of stock calculation in powerpivot and struggling to get it to work. In excel the calcualtion would be alonf the lines of:

    if(Average 4 weeks forward forecast>0) (TEF Stock + VMI Stock) / Average 4 weeks forward sales forecast= DOS

    I need this as a measure so when I move between views, from item to vendor etc it calculates still, can anyone please help?

    Thanks

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    What does the data look like, how do you get the average of (which) 4 weeks forward forecast, and what is DOS?

  3. #3
    Hi,

    Thanks for coming back to me - guess I was not very clear! Average 4 weeks forecast is a column in itself as is TEF sotck and VMI Stock. DOS is days of stock, the answer I need so can be thought of as x. So each of the names in the calculation is a column but I need the calcualtion as a measure because an individual item will give me a result but then I need the ability to aggregate items into vendor, for example, whereby all components in the calcualtion would be summed together to give me a new result. I am unable to attach a link due to forum rules but happy to explain further if I am still being unclear.

    Thanks

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,652
    Articles
    0
    Excel Version
    O365
    Just do a couple of dummy posts so that you can you post a workbook, with the data in PowerPivot, preferably with a pivot table. Or pout it on a file sharing site.

  5. #5
    Hi,

    Thanks

    Its on onedrive -https:// onedrive.live.com/redir?resid=3AE8882B3E11F58%21120

    I have added a space after the backslash to break the link. The raw data is on here plus the results I am trying to attain demonstrated in excel formulas with descriptions in text boxes. If its not clear please let me know,

    Thanks

  6. #6
    Excel 2010 with free PowerPivot and Power Query Add-Ins.
    Compatible with Office 2013 Pro Plus.
    Break up raw data into a Data Table and a Lookup Table.
    http://www.mediafire.com/view/3if7rs.../01_10_15.xlsx

  7. #7
    Hi,

    I think that has sorted it. I will validate my data fully tomorrow but on initial checks looks good - will let you know, and thank you for taking time out of your day on this.

Tags for this Thread

Posting Permissions

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