Calculating a standard Unit of Measure column

[Heather0135]

Hello,

I have a dataset that has several columns. Two of these columns are related to quantity and the unit of measure that the quantity is in. For example, one row of data may have a quantity in gallons while another row has it in liters. How would I go about creating 1 column that has all of the quanity in gallons? I know in Excel I would just do a vlookup of the original unit of measure in a table that has the unit of measure and the conversion factor to get it to the standard unit of measure that I want, and then multiply this by the quantity. Seems I am not as savvy in PBI as I am in Excel. Much thanks in advance!

[v-yalanwu-msft]

Hi, @Heather0135 ;

You could create a custom column in power query or create a  dax coulmn as follows:

1.create a custom column in power query.

if [unit] ="liters" then [quantity]*0.264172 else [quantity]

2. create a dax coulmn.

quantity3 = IF([unit]="liters",[quantity]*0.264172,[quantity])

If my understanding is incorrect, please correct me and share a screenshot of the result you want to output.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Heather0135 · Accepted solution]

I think I managed to do this by adding a conditional column with hardcoded conversion factors and then another column that does the multiplication of the quantity and conversion factor. Open to hearing if there are any more efficient/effective solutions.