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

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!