1. ## Powepivot Calculation

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. What does the data look like, how do you get the average of (which) 4 weeks forward forecast, and what is DOS?

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