PDA

View Full Version : Powepivot Calculation



Grinch356
2015-01-09, 10:48 PM
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

Bob Phillips
2015-01-10, 09:37 AM
What does the data look like, how do you get the average of (which) 4 weeks forward forecast, and what is DOS?

Grinch356
2015-01-10, 12:17 PM
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

Bob Phillips
2015-01-10, 06:52 PM
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.

Grinch356
2015-01-10, 07:12 PM
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

Herbds7
2015-01-10, 10:42 PM
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/3if7rsw074tub1j/01_10_15.xlsx

Grinch356
2015-01-11, 12:10 AM
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.