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

Accepted Solutions
Microsoft
Microsoft

Re: UOM conversion

@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
Highlighted
Super User IV
Super User IV

Re: UOM conversion

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

Re: UOM conversion

@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

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors