Forum Discussion

monojchakrab's avatar
monojchakrab
Icon for Resolver III rankResolver III
4 years ago
Solved

MID function as a variable

Total shipped qty = SUMX('Amazon YTD','Amazon YTD'[Shipped Quantity]*RELATED('summary table'[MPU])) Hi All,   I have a table which have got the product name, which includes the Pack size as par...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    4 years ago

    monojchakrab , First bring this as a column -RELATED('summary table'[MPU]) 

    and then try MID

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi monojchakrab ,

     

    You want to extract the number from the strings in Size column, right?

    It is recommended that you use the LEFT() function to get the number.

    Measure = LEFT(MAX('Table'[SIZE]),LEN(MAX('Table'[SIZE]))-6)

     

    You will find that the numbers you propose are text types, you can modify the type manually, or you can add VALUE() to change the number type.

     

     

    Best Regards,

    Stephen Tao

     

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

monojchakrab's avatar
monojchakrab
Icon for Resolver III rankResolver III
4 years ago
Total shipped qty = 
VAR _Title= SELECTCOLUMNS('Amazon YTD',"Title",'Amazon YTD'[Title]) --chooses to colum of [Title] to apply the MID function to; this is from the same table so did not have to use the Related function
VAR _SearchPack = SEARCH("of",_Title,,0)+3 --In order to find the starting position for the MID function
VAR _MPU = value(MID(_Title,_SearchPack,1)) --extracts the pack size and converts to numeric value
return 
SUMX('Amazon YTD','Amazon YTD'[Shipped Quantity]*_MPU) --iterates over [shipped qty] and multiplies by the pack size to arrive at the volume

amitchandak - Amit, I rewrote the code, and did not use the RELATED function as I am pulling the String column ([Title]) fromt the same table.

But this one is returning an error as follows :

Any reason why and how to get out of it?

Thanks