locked
Complex lookups from pivot tables RRS feed

  • Question

  • Hello all


    We´re using a pivot table to crunch extensive data, and we need to extract some data directly from the pivot table for further analysis.

    This is the pivot table:



    And this is the lookup table:



    Example => AA:

    - Current Zone Nr = 6   
    (Last price for AA is 27.48, which is within 28.22 (Zone 6 High) and 27.08 (Zone 6 Low), so the Current Zone Nr is 6)

    - Current Zone High = 28.22
    - Current Zone Low = 27.08
    - Current Zone Rank = 12

    - Prev Zone Rank = 11
    (If the Current Price Zone is #6, return the Zone Rank from Zone #5)

    - Next Zone Rank = 9
    (If the Current Price Zone is #6, return the Zone Rank from Zone #7)


    Here´s the sample file:
    http://www.mediafire.com/file/66emcugw9zu5vem/Lookups+from+Powerpivot.xlsx


    How can we do it?



    • Edited by NicoPer Sunday, October 30, 2016 2:03 AM
    Wednesday, October 26, 2016 8:02 PM

Answers

All replies

  • Wednesday, October 26, 2016 9:07 PM
  • Thanks David for stepping in.

    So I turned the powerpivot cells into formulas. I didn´t know any of that.
    (BTW it took a long time to turn just a few of the needed symbols (less than .5%) and also all the ranks were replaced by the current numbers. )

    But from there I still don´t know how to get the needed data.

    The main problem is how to get the Zone # from the Last price.
    • Edited by NicoPer Thursday, October 27, 2016 2:25 PM
    Thursday, October 27, 2016 2:29 AM
  • With Power Query (aka Get & Transform)
    Your sample file shows a Table, not a PivotTable,
    and that is what you actually want.
    http://www.mediafire.com/file/1ymef0qmm690tyi/10_27_16.xlsx
    Thursday, October 27, 2016 4:51 PM
  • Thank you very much Herbert. I saw your code step by step and it works great for static data.
    (The Price Zones table is static, updating only once per day.)

    But as dumb as I am, I now realize that I´ve should told that the "Last" price cells are RTD, streaming real time quotes. These prices are changing dynamically by the second, and we want the Lookup table data to adjust automatically. (I´m very sorry for the confusion.)

    Any ideas?


    • Edited by NicoPer Friday, October 28, 2016 7:04 PM
    Thursday, October 27, 2016 6:33 PM
  • Are we talking about Power Pivot here? This looks like a question about plain Excel.

    In Excel, you can directly refer to a cell within a pivot table, no need to transform to cube formulas first.

    Friday, October 28, 2016 7:28 AM
    Answerer
  • Thanks Michiel for stepping in.

    Whatever gets the data. I´m still struggling with it.

    Do you know how to do it?




    • Edited by NicoPer Sunday, October 30, 2016 2:01 AM
    Friday, October 28, 2016 1:11 PM
  • Sunday, October 30, 2016 9:57 AM
  • With Power Query (aka Get & Transform)
    Your sample file shows a Table, not a PivotTable,
    and that is what you actually want.
    http://www.mediafire.com/file/1ymef0qmm690tyi/10_27_16.xlsx

    Herbert, you're invited to join us as an Answerer: https://social.technet.microsoft.com/Forums/sqlserver/en-US/d512e94b-94cd-4c43-b435-708c915e5283/needed-more-answerers?forum=sqlkjpowerpivotforexcel

    The basic idea is helping us propose and mark answers. Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, October 30, 2016 9:59 AM
  • Are we talking about Power Pivot here? This looks like a question about plain Excel.

    In Excel, you can directly refer to a cell within a pivot table, no need to transform to cube formulas first.

    Michael, you're invited to join us as an Answerer to help propose and mark answers, if you're interested:

    https://social.technet.microsoft.com/Forums/sqlserver/en-US/d512e94b-94cd-4c43-b435-708c915e5283/needed-more-answerers?forum=sqlkjpowerpivotforexcel

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Sunday, October 30, 2016 10:00 AM
  • So Power Query and transforming to cube formulas seem unfit because the data needs to be updated by the second based on RTD (real time quotes) cells ("Last" column).

    Can anyone please throw some light on how to get the data with plain excel formulas?
    I´d highly appreciate it.

    Monday, October 31, 2016 2:16 AM
  • Hi NicoPer,

    this is something for an Excel forum I think, but anyway: if you need real time or near real time results, the data is probably somewhere in a database on the backend. With Excel, you can connect directly to that database. This way, no data is loaded into Excel except for the data that actually lands in the Excel table or Excel pivot table.

    This would work best while using a SSAS multidimensional or tabular model, which can be queried directly through a pivot table. But it's not an easy thing to do.

    In Excel, you can 'copy' a value from a pivot table to another cell just by entering a cell reference. You'll see a rather complicated formula if you just enter '=' and click a cell in the pivot table (this formula extracts the value using the pivot table logic). But you can also just enter the direct cell reference, e.g. '=F3'. You'll need to keep the size and structure of the pivot table constant, however.

    Monday, October 31, 2016 11:05 AM
    Answerer
  • Thanks Michiel

    I don´t want to over complicate things.

    Say we want to extract the first column "Current Zone Nr" from the current power pivot table in the sample.
    This is probably the most difficult one in my opinion.

    Example => AA:

    - Current Zone Nr = 6   
    (Last price for AA is 27.48, which is within 28.22 (Zone 6 High) and 27.08 (Zone 6 Low), so the Current Zone Nr is 6)

    How can we do that?

    Monday, October 31, 2016 2:42 PM
  • You're losing me here. "Current Zone Nr" is a column in your lookup table, not in your pivot table.

    But anyway, "=Lookups!C2" in a cell in Excel would result in the value 6. This is probably too simple for your case...

    Tuesday, November 1, 2016 10:44 AM
    Answerer
  • That´s the sample (the only row filled), sorry for the confusion.

    We want to place the data from the pivot table there, in the lookup table, as the sample shows.

    Tuesday, November 1, 2016 1:22 PM
  • To get the “Current Zone Number” isn’t a simple lookup, but a sort of a bucketing challenge.

    Not sure, if you can even solve this with cubeformulas. But even if, they might be slower than Excel.

    If performance is really an issue here, I’d strongly recommend to move this over to the Excel-forum, as the experts there might be able to tell you which would be fastest. For a start, I’d recommend to change the design of your pivot-table and move Zone Name to rows and values into the columns.

    Then for the demanding “Current Zone Number” you’d have the following options:

    1. VLOOKUP with TRUE: This is made for a task like this, but as it has to operate on a SYMBOL-level (filter), you’d have to MATCH/OFFSET the area so that only the matching SYMBOL is covered (this might be slow)
    2. SUMIFS: Kind of simple-stupid alternative: Make sure that the filters only return 1 row, so that the SUM doesn’t hurt, like:

    SUMIFS(PivotColumnWith”Zone Number”,

    PivotColumnWith”SYMBOL” , [Symbol] ,

    PivotColumnWith”Zone Low” ,"<="&[Last],

    PivotColumnWith”Zone High” ,">="&[Last]

    )

    Where you have to replace all expressions that start with "PivotColumnWith" by the matching columns of your sheet with the pivot-table. The expressions in square brackets are structured cell-references of your lookup-table. 

      3. The same logic as 2) can be applied with AGGREGATE, maybe this is faster

    For the other columns you can use simple VLOOKUPs or SUMIFS depending on performance.


    Imke Feldmann TheBIccountant.com


    Tuesday, November 1, 2016 8:15 PM
    Answerer
  • Hi Imke and thanks so much for stepping in :)

    I´ve changed the zones to rows and values into columns. Now it looks like this:




    But the rest of the process you described looks like way over my knowledge.

    Maybe you can specify a bit more or provide an example?

    Here´s the new sample file if it helps:
    http://www.mediafire.com/file/d93p6a3dkyxp7m7/Lookups+from+Powerpivot+edited.xlsx
    Tuesday, November 1, 2016 10:22 PM
  • Hi Nico,

    the symbols should have stayed where they are.

    Sorry, now further help from me here.


    Imke Feldmann TheBIccountant.com

    Wednesday, November 2, 2016 5:16 AM
    Answerer
  • Ahh now I see.. Like this:



    It does look cleaner.
    Wednesday, November 2, 2016 6:06 PM
  • Better :-)

    Next you need to show the SYMBOL in every row now.

    Also drag the Zone number into the pivot-table (either as measure or into the row-section)


    Imke Feldmann TheBIccountant.com

    Wednesday, November 2, 2016 6:23 PM
    Answerer
  • Ok, Symbol in every Row, checked.


    Now, by zone number you mean Zone name or Zone rank?
    Currently the Zone name is in the rows section and I cannot place the Zone rank (zone volume showed as rank) in the rows section.


    Wednesday, November 2, 2016 6:55 PM
  • What we're trying to do is to return the value in C2 in your lookup-table, which you named "Current Zone Number". This is a number field and we don't have it in the pivot yet. As far as I remember, it's a column in your data model hidden from client tools, so you might need to unhide it.

    Imke Feldmann TheBIccountant.com


    Thursday, November 3, 2016 6:47 AM
    Answerer
  • But the "Current Zone Number" cannot be in the data model already because it is constructed using the "Last" price.

    The "Last" price is RTD data, streaming real time quotes by the second.

    Every column in the lookup table come directly from this "Last" (current) stock price.
    Thursday, November 3, 2016 1:31 PM
  • OK, you won :-)

    Here comes the file: https://onedrive.live.com/edit.aspx?cid=de165ddf5d02daff&page=view&resid=DE165DDF5D02DAFF!80214&parId=DE165DDF5D02DAFF!107&app=Excel

    Blue is SUMIFS and orange contains some cubefunctions. But as your Rank is an implicit measure anyway, this doesn't seem to make much sense here anyway.

    Be aware that there is a new measure in your model:

    Zone Number:=IF(COUNTROWS(VALUES(Zone[Zone Order]))=1;VALUES(Zone[Zone Order]))


    Imke Feldmann TheBIccountant.com

    • Marked as answer by NicoPer Thursday, November 3, 2016 6:43 PM
    Thursday, November 3, 2016 3:27 PM
    Answerer
  • OMG! You really did it.. I still cannot believe it.
    It´s working perfectly with 400 symbols:




    I still got to review and fix the special cases like when Last is above Zone1 high and such, but I think I could do that easily with nested ifs.

    Imke, you simply rock..
    Thanks a million :)

    Thursday, November 3, 2016 6:43 PM
  • Thx & you're welcome :-)

    BTW: Just found this interesting article: http://www.excel-university.com/multi-column-lookup-with-vlookup-and-sumifs/ showing how this task could have been done even to return a text-value (so just the zone Name without the additional number-measure).


    Imke Feldmann TheBIccountant.com

    Friday, November 4, 2016 8:34 AM
    Answerer