Forum Discussion

raima7's avatar
raima7
New Member
10 years ago
Solved

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

  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    raima7

     

    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. :)

5 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    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]

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee
    10 years ago

    raima7

     

    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. :)

    • neerajvavi's avatar
      neerajvavi
      New Member
      8 years ago

      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.

      • baranba's avatar
        baranba
        Regular Visitor
        6 years ago

        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!

  • Jessikampos's avatar
    Jessikampos
    New Member
    9 years ago

    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!