how to get sum with specific data

[rahul632soni]

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 

[rahul632soni]

@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

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[rahul632soni]

@lbendlin 

Hey Below is the  table with data which is availabe

Header_Name	vin	Instance-Flag	ENG_HOURS
A	HAJSR22HKMG679922	1	202.1
A	HAJSR22HKMG679922	1	202.2
A	HAJSR22HKMG679922	1	202.3
A	HAJSR22HKMG679922	1	202.4
A	HAJSR22HKMG679922	1	208.7
A	HAJSR22HKMG679922	1	208.8
A	HAJSR22HKMG679922	1	208.9
A	HAJSR22HKMG679922	1	209
A	HAJW210HKMG679924	1	1153.9
A	HAJW210HKMG679924	1	1154
A	HAJW210HKMG679924	1	1154.1
A	HAJW210HKMG679924	1	1154.2
A	HAJW210HKMG679924	1	1154.3
A	HAJW210HKMG679924	1	1234.2
A	HAJW210HKMG679924	1	1234.3
A	HAJW210HKMG679924	1	1234.4
A	HAJW210HKMG679924	1	1234.5
A	HAJW210HKMG679924	2	100
A	HAJW210HKMG679924	2	100.1
A	HAJW210HKMG679924	2	100.2
A	HAJW210HKMG679924	2	100.3
A	HAJW210HKMG679924	2	100.4
A	HAJW210HKMG679924	2	199.7
A	HAJW210HKMG679924	2	199.8
A	HAJW210HKMG679924	2	199.9
A	HAJW210HKMG679924	2	200
B	HAJSR22HKMG679922	1	241
B	HAJSR22HKMG679922	1	241.1
B	HAJSR22HKMG679922	1	241.2
B	HAJSR22HKMG679922	1	241.9
B	HAJSR22HKMG679922	1	242
B	HAJSR22HKMG679922	1	242.1
B	HAJSR22HKMG679922	1	259.4
B	HAJSR22HKMG679922	1	259.5
B	HAJSR22HKMG679922	1	259.6
B	HAJSR22HKMG679922	1	259.7
B	HAJSR22HKMG679922	1	259.8
B	HAJSR22HKMG679922	1	259.9
B	HAJSR22HKMG679922	1	260
B	HAJSR26HLMG679906	1	739.4
B	HAJSR26HLMG679906	1	739.5
B	HAJSR26HLMG679906	1	739.6
B	HAJSR26HLMG679906	1	739.7
B	HAJSR26HLMG679906	1	739.8
B	HAJSR26HLMG679906	1	739.9
B	HAJSR26HLMG679906	1	766.5
B	HAJSR26HLMG679906	1	766.6
B	HAJSR26HLMG679906	1	766.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_Name	Header Hours
A	187.5
B	46.3

Regrads

Rahul

[lbendlin]

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