Regrouping/binning ranged data

[maxdtraff]

I have speed data and vehicle counts pulling via SQLserver directquery which deals with data from several different locations. A count of vehicles is given grouped by speed bin for each location but each location has different speed ranges for their bins. I am trying to find a way to create a new set of uniform speed bins so that I can compare speeds by count across locations. i.e. from the table below, for location 1 the count of values for speed bins Speed 0, Speed1 & Speed2 should combine into CountOfSpeed0. How could I achieve this? Thanks!

Current

Lower speed Range mph	Speed0	Speed1	Speed2	Speed3	Speed4	Speed5	Speed6	Speed7	Speed8
Location 1	0	30	35	40	45	50	55	60	65
Location 2	0	5	10	15	20	25	30	35	40
Location 3	0	40	45	50	55	60	65	70	75

Desired

Lower speed Range mph	Speed0	Speed1	Speed2	Speed3	Speed4	Speed5	Speed6	Speed7	Speed8
Location 1	0	40	45	50	55	60	65	70	75
Location 2	0	40	45	50	55	60	65	70	75
Location 3	0	40	45	50	55	60	65	70	75

[amitchandak]

@maxdtraff , are this calculations in first table in power bi or came from DB.

Looking at table 2, it looks like a set of new measures

like

new speed1 = calculate(max(Table[speed1]), all(Table))

new speed2 = calculate(max(Table[speed2]), all(Table))

[maxdtraff]

These aren't calculations in the first table, they are the boundaries for the speeds that the CountOfSpeed(0-8) will calculate and come directly from the server. I am afraid I am explaining badly as I have given myself a headache trying to sort this today!.

Every vehicle travelling below the speeds given in the Columns labelled Speed0-8 will be counted in the corresponding CountOfSpeed(0-8). So if 3 cars pass Location 1 travelling at 30mph (falling into catgory Speed1) they will appear in CountOfSpeed1 as a value 3. I need for the CountOfSpeed calculation to group the counts when they fall into speed bins that are below 40mph into CountOfSpeed0. If that makes sense?