Forum Discussion
Regrouping/binning ranged data
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 |
2 Replies
- amitchandak
Super User
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))
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?
