Need to Calculate conversion quantity in Power BI

[SBC]

Hi Folks,

I have a requirement to convert all measurementquantityamount  of MT,M3,USG to BBL,

we need to  show all MT,M3,USG  quantityamount  in BBL quantityamount.

Example:

1 BBL = 0.1183432 MT,

1 BBL= 0.159 M3,

1BBL = 42 USG

Below is the Screeenshot of source in different formats MT,M3,USG and BBL need to convert to  all to BBL measuremtnquantityamount .

We need to show all M3,MT,USG  in BBL quantityamount, Need solution in  DAX  or method to achieve this.

Thanks,

SBC

[tamerj1 · Accepted solution]

Hi @SBC 
Another way is to have a "Conversion" table linked with your table

Measuring Unit	Converion Factor
MT	0.1183432
M3	0.159
USG	42
BBL	1

Then you can link Converion[Measuring Unit] with TableName[MsrmntQtyUom] (One - Many single way realtionship)

Then use 
BBLMsrmntQtyAmt = TableName[MsrmntQtyAmt] * RELATED ( Converion[Converion Factor] )

[SBC]

Hi @tamerj1 ,

I have tried this method ,works fine for MT,M3 and USG,But for BBL it is showing blank values instead of displaying the  associated values .as below screenshot.

Thanks,

SBC

[tamerj1]

@SBC 

Check the spelling. Could be spaces

[Fowmy]

@SBC 

You can add a calculated column as follows:

Conversion  = 
 VAR __FACTOR = SWITCH ( 'table'[MsrmntQtyUom], "MT", 0.1183432, "M3" = 0.159, "USG" = 42, "BBL", 1 )
    VAR __RESULT = DIVIDE('table'[MsrmntQtyAmnt], __FACTOR )
    RETURN
        __RESULT