Powerpivot LOOKUPVALUE within the same table

[nikkollai]

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]

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

 

[nikkollai]

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]

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]

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]

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