Forum Discussion

gaikwadaa123's avatar
gaikwadaa123
Helper II
5 years ago
Solved

Percentage between two columns

Hello, 

 

I have two tables as below 

Table 1: Actual Data 

Table 2 :  History Data. 

 

 

I would like to caluculate the percentage error  between the two columns DBDryBulb-A and DBDryBulb_F

 from the above two coulumns 

Formula shoud be 

(DBDryBulb-A - DBDryBulb_F)/ DBDryBulb-A 

I will convert it later into percentage. But would like to get absolute error value by using above two columns. 

 

Thank you. 

 

 

 

 

Need Help
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello gaikwadaa123 

     

    you can join both tables by the datetime column and then expand your F-Column and then the needed calculation

    Here an example

    let
    A = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyNDBQNDKwMDJR0lQ6VYHVQJI4iEkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, #"DBDryBulb-A" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"DBDryBulb-A", Int64.Type}}, "de-DE")
    in
        #"Changed Type",
    B= 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyNDBQNDKwMDJR0lY6VYHVQJI4iEiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, #"DBDryBulb-F" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"DBDryBulb-F", Int64.Type}}, "de-DE")
    in
        #"Changed Type",
    JoinTables= Table.NestedJoin
    (
        A,
        "DateTime",
        B, 
        "DateTime",
        "B",
        JoinKind.LeftOuter
    ),
    #"Expanded B" = Table.ExpandTableColumn(JoinTables, "B", {"DBDryBulb-F"}, {"DBDryBulb-F"}),
    #"Added Custom" = Table.AddColumn(#"Expanded B", "Custom", each ([#"DBDryBulb-A"]-[#"DBDryBulb-F"])/[#"DBDryBulb-A"])
in
    #"Added Custom"

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

  • v-yingjl's avatar
    v-yingjl
    5 years ago

    Hi gaikwadaa123 ,

    After merging two tables, please detect the data type for each column, if the data type is wrong, it could cause the wrong calculation result.

     

    Best Regards,
    Community Support Team _ Yingjie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello gaikwadaa123 

     

    you can join both tables by the datetime column and then expand your F-Column and then the needed calculation

    Here an example

    let
    A = 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyNDBQNDKwMDJR0lQ6VYHVQJI4iEkVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, #"DBDryBulb-A" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"DBDryBulb-A", Int64.Type}}, "de-DE")
    in
        #"Changed Type",
    B= 
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUAyIjAyNDBQNDKwMDJR0lY6VYHVQJI4iEiVJsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, #"DBDryBulb-F" = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateTime", type datetime}, {"DBDryBulb-F", Int64.Type}}, "de-DE")
    in
        #"Changed Type",
    JoinTables= Table.NestedJoin
    (
        A,
        "DateTime",
        B, 
        "DateTime",
        "B",
        JoinKind.LeftOuter
    ),
    #"Expanded B" = Table.ExpandTableColumn(JoinTables, "B", {"DBDryBulb-F"}, {"DBDryBulb-F"}),
    #"Added Custom" = Table.AddColumn(#"Expanded B", "Custom", each ([#"DBDryBulb-A"]-[#"DBDryBulb-F"])/[#"DBDryBulb-A"])
in
    #"Added Custom"

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.


    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • gaikwadaa123's avatar
      gaikwadaa123
      Helper II
      5 years ago

      I tried to append the tables and got the two columns in same table 

      I used the  formula 

      Measure 2 = SUM('Actual Data'[DBDryBulb-A])-SUM('Actual Data'[DBDryBulb _F])

      But still the substarction is not looking correct. can you suggest me formula 

       

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello gaikwadaa123 

         

        i tried to reproduce your scenario with the example above and its working fine

        so the approach and formula is correct but probably something in your dataset that is causing the wrong outcome

         

        If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
        Kudoes are nice too

        Have fun

        Jimmy

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    Can you describe your model further?  Do you have separate tables for DateTime values?  One with your Sensors(?) ID values?  I would encourage you to add both those tables if not already, so you can connect to the two tables you show above.  If the columns are the same in both F and A tables, you could also consider appending them (with a column to indicate if it is an F or A value).  Either of the above suggestions would make your calculation simpler.

     

    Pat