cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
neerajvavi
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.

Thanks in advance.

1 ACCEPTED SOLUTION
Eric_Zhang
Microsoft
Microsoft

@neerajvavi

You may need a midtable and relationship as below.

Capture.PNG

 

Capture.PNGt

 

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] )

UOM.gif

 

See more in the attached pbix file.

 

View solution in original post

2 REPLIES 2
Eric_Zhang
Microsoft
Microsoft

@neerajvavi

You may need a midtable and relationship as below.

Capture.PNG

 

Capture.PNGt

 

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] )

UOM.gif

 

See more in the attached pbix file.

 

Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors