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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.