Get the measure value from other row with similar code as first row

[alexa_0028]

Hi All,

I am trying to write the measure "Convert Sales To sterile " like below :

Logic: If name contains "S", then keep ASP measure as it is.
If it doesnt contains "S", then bring ASP of the other row which contains P. Code  as the row but has S in the end.

Example: How can I put the value 362.75 in the first row as well?

I am trying below measure but no luck so far.

Convert Sales To sterile =

CALCULATE([ASP (NTS|PU)],ALL('Sterile Map'[P. Code]),'Sterile Map'[new_product_column] IN VALUES('Sterile Map'[P. Code]))

[v-chenwuz-msft]

Hi @alexa_0028 ,

Please share the code of [ASP (NTS|PU)].

Or you can refer this measure and do some change for your measure Convert Sales To sterile.

Measure =
VAR _nos =
    CALCULATE(
        SUM( 'Table'[B] ),
        FILTER(
            ALL( 'Table' ),
            [A] = CONCATENATE( SELECTEDVALUE( 'Table'[A] ), "s" )
        )
    )
VAR _withs =
    SUM( 'Table'[B] )
RETURN
    IF( ISBLANK( _nos ), _withs, _nos )

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alexa_0028]

Hi @v-chenwuz-msft 
Thanks for the solution, but how can you fix the grand total? 
It should be 12 but not 10.

I tried this formula suiting to my situation and it worked but I have the same issue.Grand total didnt match with rows above.

Convert to Sales =
CALCULATE([ASP (NTS|PU)],ALLEXCEPT('Sterile Map','Sterile Map'[new_P.Code]),'Sterile Map'[P. Code] IN VALUES('Sterile Map'[new_P.Code]))

I tried -
IF(hasonefilter([P. Code]),convert_to_Sale,SUMX(keepfilter(VLAUES(P. Code])),convert_to_Sale) but no luck

[alexa_0028]

@Greg_Deckler 
I have formatted the question again, can you please help me?
I am not sure if Vlookup will work here as ASP is a measure.

[Greg_Deckler]

@alexa_0028 So normally you would use either LOOKUPVALUE or something like MAXX(FILTER(..., ...), ...) to emulate VLOOKUP. Hard to be more specific with the information provided.