MID function as a variable

[monojchakrab]

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 part of the text string :

I have inserted a column with the MID function which extract the pack size from the string.

But now I am trying to use the MID function in a variable, so that I can multiple the shipped qty with the pack size (Called "MPU" here) and get the total qty :

Total shipped qty =

SUMX('Amazon YTD','Amazon YTD'[Shipped Quantity]*RELATED('summary table'[MPU]))

But when I am trying to add the MID function in a variable, it is not allowing me to choose a column on which to perform the MID search.

Can the MID function be used a quasi-iterator in a measure, instead of in a calculated column only?

Thanks for the help in anticipation

regds.,

[amitchandak · Accepted solution]

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

and then try MID

[v-stephen-msft · Accepted solution]

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]

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

[amitchandak · Accepted solution]

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

and then try MID