locked
Simple Powerpivot Formula RRS feed

  • Question

  • Hi,

    I am trying to calculate days of stock as a measure in pwerpivot as a measure.

    In simple terms the calculation is:

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

    SO i have columns for these three things but I need this to calcualte as a measure so as I roll up from item level to vendor, total etc I am getting a recalcualted number. I also then need a second measure that instead of recalculating takes an average of each items DOS and averages that out.

    The below measure I created does not roll up correctly:

    DOSinc VMI Measure:=AVERAGEX(FILTER(SUPPDCNEW,SUPPDCNEW[Average 4 week Forecast]>0),(SUPPDCNEW[TEF Stock]+SUPPDCNEW[VMI Stock])/SUPPDCNEW[Average 4 week Forecast])

    Can anyone please support?

    Friday, January 9, 2015 3:45 PM

Answers

  • Hi,

    Sorted now so you dont need to check this one anymore, thanks for looking into it though.

    • Marked as answer by Grinch356 Monday, January 12, 2015 2:50 PM
    Monday, January 12, 2015 2:50 PM

All replies

  • The logical representation you've laid out does not map directly to the functions you have chosen to implement.

    AVERAGEX() is an iterator. It steps row-by-row through the table you pass it, evaluates the expression you pass as the second argument, and adds those to an accumulated sum. At the end it divides this accumulated sum by the number of rows in the table you passed it in the first argument.

    Could you provide a sample of your data and what your desired outcome looks like?

    Friday, January 9, 2015 4:17 PM
  • Hi,

    Thanks for the quick respone. The link is below.

    On the forst tab you can see how I calcualte DOS at item level (the table name is SuppDCNew).

    On tab 2 I have given an example on method 1 aggregating by showing how it sums up if I look at a specific family or vendor.

    Back on tab 1 you can see a box called method 2 - this is the second measure I am trying to get wher instead of summing it takes an everage of each indivdual SKU DOS at a given level to produce the result.

    Please let me know if this is not clear enough for you.

    Thanks


    • Edited by Grinch356 Friday, January 9, 2015 6:35 PM
    Friday, January 9, 2015 4:49 PM
  • Sorry - that was the wrong link! Corrected!

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

    Friday, January 9, 2015 6:24 PM
  • Hi Greg 2178,

    Is the information I attached ok - im guessing what I need to do cant be done perhaps? If so I guess I find a plan B!

    Friday, January 9, 2015 9:13 PM
  • Hi,

    Sorted now so you dont need to check this one anymore, thanks for looking into it though.

    • Marked as answer by Grinch356 Monday, January 12, 2015 2:50 PM
    Monday, January 12, 2015 2:50 PM
  • Hi Grinch,

    It seems that your issue had been solved. Could you share the solution which will help other forum members who have the similar issue.

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, January 13, 2015 1:46 AM