Forum Discussion

rahul632soni's avatar
rahul632soni
Helper I
3 years ago

how to get sum with specific data

 

I have this data available , now i want to calculate Header Hours which is Difference of Max and Min Engine HOurs , However it is working with Same level of Grouping as we have Engine Hours Details on Vin Level , hence when i select only header A i want to get total Header Hours as around 211 Hours , however when i am doing it on Header Level i am getting total Header Hours as 1134 Hours as shown below 

 

How can i achieve this .

Let me know for any other question related to data .

Regards

Rahul 

 

Need Help

5 Replies

    • rahul632soni's avatar
      rahul632soni
      Helper I
      3 years ago

      lbendlin 

      Hey Below is the  table with data which is availabe

      Header_NamevinInstance-FlagENG_HOURS
      AHAJSR22HKMG6799221202.1
      AHAJSR22HKMG6799221202.2
      AHAJSR22HKMG6799221202.3
      AHAJSR22HKMG6799221202.4
      AHAJSR22HKMG6799221208.7
      AHAJSR22HKMG6799221208.8
      AHAJSR22HKMG6799221208.9
      AHAJSR22HKMG6799221209
      AHAJW210HKMG67992411153.9
      AHAJW210HKMG67992411154
      AHAJW210HKMG67992411154.1
      AHAJW210HKMG67992411154.2
      AHAJW210HKMG67992411154.3
      AHAJW210HKMG67992411234.2
      AHAJW210HKMG67992411234.3
      AHAJW210HKMG67992411234.4
      AHAJW210HKMG67992411234.5
      AHAJW210HKMG6799242100
      AHAJW210HKMG6799242100.1
      AHAJW210HKMG6799242100.2
      AHAJW210HKMG6799242100.3
      AHAJW210HKMG6799242100.4
      AHAJW210HKMG6799242199.7
      AHAJW210HKMG6799242199.8
      AHAJW210HKMG6799242199.9
      AHAJW210HKMG6799242200
      BHAJSR22HKMG6799221241
      BHAJSR22HKMG6799221241.1
      BHAJSR22HKMG6799221241.2
      BHAJSR22HKMG6799221241.9
      BHAJSR22HKMG6799221242
      BHAJSR22HKMG6799221242.1
      BHAJSR22HKMG6799221259.4
      BHAJSR22HKMG6799221259.5
      BHAJSR22HKMG6799221259.6
      BHAJSR22HKMG6799221259.7
      BHAJSR22HKMG6799221259.8
      BHAJSR22HKMG6799221259.9
      BHAJSR22HKMG6799221260
      BHAJSR26HLMG6799061739.4
      BHAJSR26HLMG6799061739.5
      BHAJSR26HLMG6799061739.6
      BHAJSR26HLMG6799061739.7
      BHAJSR26HLMG6799061739.8
      BHAJSR26HLMG6799061739.9
      BHAJSR26HLMG6799061766.5
      BHAJSR26HLMG6799061766.6
      BHAJSR26HLMG6799061766.7

      This Talks about that a Particular Header was attached to a VIn during which Engine Hours .

      we need Total Engine Hours for Each Header , Engine Hours can be calculated using Difference of Max Engine Hours  and Min Engine Hours For Each VIn and Each Instance for Each Header .

      Below is the Output Required 

      Header_NameHeader Hours
      A187.5
      B46.3

      Regrads

      Rahul

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago
        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdK7DsMgDAXQf2GOEJinx3Rp1MfSDh2i/P9vlLwqhRK4Aws6suxrj6PoRSeG/vZ+EQ3359UHZqL0p9MjRVKLqWsrgpSBlAVUlAFSEVIMqIP5kFY/YzejtTMSYhZCx+jPGWHMtBkZqNrMwGrApDNzVbYsQSnENDLbVX3IXdVn3FV9xEUxH8/1VEVI1W+MlnNd07rUTtpqxEhMEaQYUEglQrpyvO2mpRykPKQCpCKkgLR8vmc/PFaj/GaC+c+hrPIcyirPoazyHMoqz6Gs8hwKynuk+6SA7pNK3U9f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Header_Name = _t, vin = _t, #"Instance-Flag" = _t, ENG_HOURS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ENG_HOURS", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Header_Name","vin","Instance-Flag"}, {{"Min Hours", each List.Min([ENG_HOURS]), type number}, {"Max Hours", each List.Max([ENG_HOURS]), type number}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Header Hours", each [Max Hours]-[Min Hours]),
    #"Grouped Rows1" = Table.Group(#"Added Custom", {"Header_Name"}, {{"Header Hours", each List.Sum([Header Hours]), type number}})
in
    #"Grouped Rows1"

        How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

  • rahul632soni's avatar
    rahul632soni
    Helper I
    3 years ago

    lbendlin 

    Super Thanks its working actually , 

    however just one more input, Instead of creating a table with the provided data , because that was just sample data

    in below image it can be seen that we have a table which have all these variable , so can we use directly from here and create new table so that it would be dynamic.

    in image we can see the table name and all other variable which i showed as sample data.

     

    Thanks in Advance 🙂

    Regards

    Rahul