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   