Forum Discussion

neerajvavi's avatar
neerajvavi
New Member
8 years ago
Solved

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            ...
  • Eric_Zhang's avatar
    Eric_Zhang
    8 years ago

    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's avatar
Ashish_Mathur
Super User
8 years ago

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.