Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
raima7
New Member

Function convert in Power BI

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

1 ACCEPTED SOLUTION
Eric_Zhang
Employee
Employee

@raima7

 

Supposing there're trans and rates tables as below

Capture.PNG

 

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.

Capture.PNG

 

Choose lbs

Capture.PNG

 

Choose tons

Capture.PNG

 

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. 🙂

View solution in original post

5 REPLIES 5
Jessikampos
New Member

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!

Eric_Zhang
Employee
Employee

@raima7

 

Supposing there're trans and rates tables as below

Capture.PNG

 

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.

Capture.PNG

 

Choose lbs

Capture.PNG

 

Choose tons

Capture.PNG

 

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.

 

PRODUCTALTERNATIVE UNRATEBASE UNNET WEIGHTKEY
3274CAR0.25ST4.253274_CAR
21324PAL0.1ST4.2521324_PAL
2134EA1ST4.252134_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!

ankitpatira
Community Champion
Community Champion

@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]

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.