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
Microsoft

@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
Microsoft

@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.

Super User

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors