Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello everybody, 🙂
I'm trying to assign my datas to a unit of measure (kgs, lbs, tons) and I would like to convert them.
Someone would have any idea?
Thanks
Solved! Go to Solution.
Supposing there're trans and rates tables as below
Create a measure as
Total Mass = IF ( ISFILTERED ( Rate[TO_UNIT] ) && HASONEVALUE ( Rate[TO_UNIT] ), SUMX ( Trans, Trans[AMOUNT] * LASTNONBLANK ( Rate[RATE], "" ) ) & " " & LASTNONBLANK ( Rate[TO_UNIT], "" ) & "s", SUMX ( Trans, Trans[AMOUNT] * LASTNONBLANK ( 'Default Rate'[RATE], "" ) ) & " " & LASTNONBLANK ( 'Default Rate'[TO_UNIT], "" ) & "s" )
Then use a Slicer to determine the converting UNIT. By default, it shows as kgs.
Choose lbs
Choose tons
Check more details in the attached pbix. If it answers your question, please accept it as solution. For any question, feel free to let me know. 🙂
Please,
Could someone tell me how I can do a functoin to divide a number by 1000,00.
For exemple: I need to convert 1000,00 Kg in 1 TO.
I habe just one column. I've tried to do what raima7 said, but I didn't have success.
Thank you!
Supposing there're trans and rates tables as below
Create a measure as
Total Mass = IF ( ISFILTERED ( Rate[TO_UNIT] ) && HASONEVALUE ( Rate[TO_UNIT] ), SUMX ( Trans, Trans[AMOUNT] * LASTNONBLANK ( Rate[RATE], "" ) ) & " " & LASTNONBLANK ( Rate[TO_UNIT], "" ) & "s", SUMX ( Trans, Trans[AMOUNT] * LASTNONBLANK ( 'Default Rate'[RATE], "" ) ) & " " & LASTNONBLANK ( 'Default Rate'[TO_UNIT], "" ) & "s" )
Then use a Slicer to determine the converting UNIT. By default, it shows as kgs.
Choose lbs
Choose tons
Check more details in the attached pbix. If it answers your question, please accept it as solution. For any question, feel free to let me know. 🙂
Hi,
This method does not work if we have multiple records in Trans table with same UOM code. What I am looking for is as below:
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
HOW TO GET THIS WORKING, IF I NEED TO CREATE A MEASURE THAT CONVERTS ALL THE RECORDS IN THE TRANSACTION TABLE TO THE SAME UOM?
Thanks in advance.
I have a simmilar scenario. So far I have created a Rates Table containing all the products and possible units conversion rates.
PRODUCT | ALTERNATIVE UN | RATE | BASE UN | NET WEIGHT | KEY |
3274 | CAR | 0.25 | ST | 4.25 | 3274_CAR |
21324 | PAL | 0.1 | ST | 4.25 | 21324_PAL |
2134 | EA | 1 | ST | 4.25 | 2134_EA |
I added a KEY to my Transaction Table as it is in the Rate Table.
As you can imagine - I have made a relationship "many to many - both" between the Tables.
Then the measure that is working pretty well was following:
QTY ORDERED IN KG =
VAR QTYBASEUN = SUM('TRANSACTION'[QTY ORDERED])/FIRSTNONBLANK('RATES'[Rate],'RATES'[Rate])
RETURN
QTYINBASEUN*FIRSTNONBLANK('RATES'[Net Weight],'RATES'[Net Weight])
I had to add a Variable to standardize all alternative units to base unit of measure.
As I had a net weight of each Product Base Unit of Measure - I was able to convert in into KG which was my objective here.
This is a very quick and ugly way but I did not have time to discover more elegant solution.
I only had a day to fetch the data from ERP systems and transfrom it before passing to Power Bi and creating a dashboard.
If anyone would find a more memory efficient sollution - it will be much appreciated!
@raima7 I think you have to make use of meaures or calculated columns in power bi desktop and usign formula in DAX conver them. For example for converting from Kilograms to Pounds you can create calculated column with DAX formula as,
[YourKilogramColumn] * 2.2 = [CalculatedColumnInPounds]
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |