Powerpivot LOOKUPVALUE within the same table
Results 1 to 6 of 6

Thread: Powerpivot LOOKUPVALUE within the same table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Powerpivot LOOKUPVALUE within the same table

    Hello,

    I am not new to excel but pretty new to PowerPivot and DAX. This particular project requires a powerpivot.

    In powerpivot i have a table called PRODUCTS with two columns [CODEPROD] and [QUANTITY] there i created a new column [NEWCODES] and ran all my records to modify certain product codes that end with 3 letters "HDG" than i take off "HDG" and repalced with "G" . For example RR-WT-5T-HDG ends up RR-WT-5TG . I do this with this formula (may be a little crude but does the job)

    =IF(RIGHT([CODEPROD], 3)="HDG", LEFT([CODEPROD], FIND("G", [CODEPROD])-4)&"G", "")

    NOW THE CHALLENGING PART:

    I need to find the stock quantity for the newly created products RR-WT-5TG in a new column[NEWCODES]. In other words match the record from [NEWCODES] with [CODEPROD] and give me the value of [QUANTITY]

    I am trying different options with LOOKUPVALUE but nothings seems to work.


    Any ideas will be highly appreciated.
    Thank you, Nick

  2. #2
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Powerpivot LOOKUPVALUE within the same table

    Can you paste a little sample data of what you have and want? I'm... confused.
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  3. #3
    New Member
    Join Date
    Sep 2014
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Powerpivot LOOKUPVALUE within the same table

    Hi Scottsen,

    Thank you for getting back to me. I have attached a sample file .. url is below. I put it on ONEDRIVE so in order to download please do the "save as" from the file manu. I hope it will make sense what i am trying to do.



    https://onedrive.live.com/redir?resi...A2895AD5%21202

    Image file :

    [IMG][/IMG]



    Tks, Nick

  4. #4
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Powerpivot LOOKUPVALUE within the same table

    Not sure that helped.

    Do you want:
    * for codes that are not blank... the value in quantity.
    * for codes that ARE blank... the sum of all the blanks?

    I mean, you can just put =[Quantity] in your G_Quantity, if will read from "the same row"... but then, you would just use Quantity directly.
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

  5. #5
    Board Regular
    Join Date
    Apr 2014
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Powerpivot LOOKUPVALUE within the same table

    I think I get what you want. The Code in the first row is wrong and should be KK-2T-2.5G instead and this should sum up with the KK-2T-2.5G from row 6 for example.

    The easiest way would be to create one column with the real code for all rows like REALCODE =IF(NEWCODE="",CODEPROD,NEWCODE) and use this REALCODE column in your pivot to sum up all quantities

  6. #6
    Board Regular scottsen's Avatar
    Join Date
    Mar 2014
    Location
    Seattle, WA
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Powerpivot LOOKUPVALUE within the same table

    Oh geez, I didn't even realize the NEWCODE mapped to a different row in CODEPROD. My bad.

    LOOKUPVALUE should work fine... =LOOKUPVALUE(Table1[Quantity], Table1[CODEPROD], Table1[NEWCODES])

    What happens?!
    scott at tinylizard.com : Power Pivot Consultant, Trainer and Mercenary
    See my Power Pivot blog at http://tinylizard.com/blog

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •