locked
Fill in blank cells of a Pivot Table Value field area RRS feed

  • Question

  • Hi,

    In this workbook (http://1drv.ms/1oHk0QV), a normal Pivot Table has been created on the "Pivot Table" worksheet.  I'd like the blank cells of the Pivot Table to be filled up with the preceding non blank value.  So to take an example of Product A, the following is what I want:

    1. H5:N5 should have 1
    2. P5:S5 should have 2
    3. U5:W5 should have 2
    4. Y5:BA5 should have 3

    The same should be done for the others as well.

    I have attempted something on the "PowerPivot" worksheet but I do not get the correct result.

    Please help.


    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Thursday, September 4, 2014 12:19 PM

Answers

  • there you go:

    YourMeasure:=CALCULATE([State value],
    LASTNONBLANK(FILTER(ALL(Table2[Week]), Table2[Week] <= MAX(Table2[Week])), [State value])
    )

    it basically finds the last week with a valid [State Value] and shows it

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Friday, September 5, 2014 8:21 AM
    • Marked as answer by Ashish Mathur Friday, September 5, 2014 8:38 AM
    Friday, September 5, 2014 8:18 AM
    Answerer

All replies

  • there you go:

    YourMeasure:=CALCULATE([State value],
    LASTNONBLANK(FILTER(ALL(Table2[Week]), Table2[Week] <= MAX(Table2[Week])), [State value])
    )

    it basically finds the last week with a valid [State Value] and shows it

    hth,
    gerhard


    Gerhard Brueckl
    blogging @ http://blog.gbrueckl.at
    working @ http://www.pmOne.com

    • Proposed as answer by Michael Amadi Friday, September 5, 2014 8:21 AM
    • Marked as answer by Ashish Mathur Friday, September 5, 2014 8:38 AM
    Friday, September 5, 2014 8:18 AM
    Answerer
  • Thank you.

    Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

    Friday, September 5, 2014 8:38 AM