UOM conversion

[neerajvavi]

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.

Thanks in advance.

[Eric_Zhang · Accepted solution]

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

[Ashish_Mathur]

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.