cancel
Showing results for
Did you mean:
New Member

## UOM conversion

Hi,
I need help to identify the best method for UOM conversion. Below is my requirment.

1) We have transaction table which has multiple records as below:

Product      UOM      QTY
A                KG          10
A                TON        1
B                 LB          20
C                 KG         10
C                 LB          15
D                TON        2

2) I have table with all the conversion rates a below:

FROM_UNIT        TO_UNIT        RATE
KG                        KG                   1
KG                        LB                   2.204
KG                        TON               0.001
LB                         KG                  0.453
LB                         LB                   1
LB                         TON               0.000453
TON                      KG                  1000
TON                      LB                   2204.62
TON                      TON                1

I NEED TO CREATE A MEASURE THAT CONVERTS ALL THE RECORDS IN THE TRANSACTION TABLE TO THE SAME UOM. Your help is appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions Microsoft

## Re: UOM conversion

@neerajvavi

You may need a midtable and relationship as below.  t

Then create two measures

```CONVERTED UOM =
VAR COV_RATE =
MAXX (
FILTER ( RELATEDTABLE ( Rate ), Rate[FROM_UNIT] = MAX ( Trans[UOM] ) ),
Rate[RATE]
)
RETURN
SUM ( Trans[QTY] ) * COV_RATE

converted UOM_ =
VAR tem_tbl =
ADDCOLUMNS ( Trans, "rate_", MAXX ( RELATEDTABLE ( Rate ), Rate[RATE] ) ),
"conv_UOM", Trans[QTY] * [rate_]
)
RETURN
SUMX ( tem_tbl, [conv_UOM] )
``` See more in the attached pbix file.

2 REPLIES 2
Highlighted Super User IV

## Re: UOM conversion

Hi,

Write this calculated column formula in the Transaction table to get the rate for all conversions from any unit to KG.

=LOOKUPVALUE(ConversionRate[Rate],ConversionRate[From_unit],Transaction[UOM],ConversionRate[To_Unit],"KG")

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com Microsoft

## Re: UOM conversion

@neerajvavi

You may need a midtable and relationship as below.  t

Then create two measures

```CONVERTED UOM =
VAR COV_RATE =
MAXX (
FILTER ( RELATEDTABLE ( Rate ), Rate[FROM_UNIT] = MAX ( Trans[UOM] ) ),
Rate[RATE]
)
RETURN
SUM ( Trans[QTY] ) * COV_RATE

converted UOM_ =
VAR tem_tbl =
ADDCOLUMNS ( Trans, "rate_", MAXX ( RELATEDTABLE ( Rate ), Rate[RATE] ) ),
"conv_UOM", Trans[QTY] * [rate_]
)
RETURN
SUMX ( tem_tbl, [conv_UOM] )
``` See more in the attached pbix file.

Announcements #### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section. #### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products. Top Solution Authors
Top Kudoed Authors
Users online (1,254)