# Powerpivot LOOKUPVALUE within the same table



## nikkollai (Sep 10, 2014)

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


----------



## scottsen (Sep 10, 2014)

Can you paste a little sample data of what you have and want?  I'm... confused.


----------



## nikkollai (Sep 11, 2014)

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?resid=E81EAEA2895AD5%21202

Image file : 







[/URL][/IMG]



Tks, Nick


----------



## scottsen (Sep 11, 2014)

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.


----------



## Tianbas (Sep 12, 2014)

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


----------



## scottsen (Sep 12, 2014)

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?!


----------

