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 QTYA KG 10A TON 1B LB 20C KG 10C LB 15D TON 22) I have table with all the conversion rates a below:FROM_UNIT TO_UNIT RATEKG KG 1KG LB 2.204KG TON 0.001LB KG 0.453LB LB 1LB TON 0.000453TON KG 1000TON LB 2204.62TON TON 1I 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.
Go to Solution.
You may need a midtable and relationship as below.
Then create two measures
CONVERTED UOM =
VAR COV_RATE =
FILTER ( RELATEDTABLE ( Rate ), Rate[FROM_UNIT] = MAX ( Trans[UOM] ) ),
SUM ( Trans[QTY] ) * COV_RATE
converted UOM_ =
VAR tem_tbl =
ADDCOLUMNS ( Trans, "rate_", MAXX ( RELATEDTABLE ( Rate ), Rate[RATE] ) ),
"conv_UOM", Trans[QTY] * [rate_]
SUMX ( tem_tbl, [conv_UOM] )
See more in the attached pbix file.
View solution in original post
Write this calculated column formula in the Transaction table to get the rate for all conversions from any unit to KG.
Hope this helps.
Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.
Click here to read more about the December 2021 Updates!