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.
Solved! Go to Solution.
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.
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.
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.
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
User | Count |
---|---|
210 | |
49 | |
45 | |
44 | |
42 |
User | Count |
---|---|
262 | |
211 | |
103 | |
79 | |
65 |