none
TopN/Summerize Function in PowerPivot Calculated Column

    Question

  • Hello,

    Relatively new at PowerPivot here. I am have been trying to use a combination of TopN and/or Sumemrize functions in a calculated column with no luck. I am able to get the results I need in using plain old Excel Sheet with Large/Match functions, but I need to calculate this in a PowerPivot table.

    Here is a small sample of the data :

    DATEID Week Year Value "3rd value by  dateID"
    20092 2 2009 127.8 110.6
    20092 2 2009 118.6 110.6
    20092 2 2009 110.6 110.6
    20091 1 2009 140.6 97.8
    20091 1 2009 137 97.8
    20091 1 2009 97.8 97.8
    20091 1 2009 96.9 97.8
    20082 2 2008 158.3 120.4
    20082 2 2008 131.4 120.4
    20082 2 2008 120.4 120.4
    20082 2 2008 117 120.4
    20082 2 2008 109.6 120.4
    20081 1 2008 137.5 125.1
    20081 1 2008 131 125.1
    20081 1 2008 125.1 125.1

    With the values I am trying to calculate in the column with quotes. Basically I am looking to get the third largest value by dateID so that I can refer to this in a pivot table or with other calculated columns. Please let me know if this is possible and what the formula would look like in the calculated column. Thank you.

    Wednesday, July 10, 2013 5:29 PM

Answers