Totals of a measure in a hierarchy based on the lowest level

[Yosh]

I am fairly new to Cognos and I have this difficult requirement. I am hoping someone here can help me.

I have a crosstab with a hierarchy on the row and a few measures on the column.
The hierarchy can be drilled down to 6 levels - SCENARIO>MODELYEAR>OEM>MAKE>MODEL>VIN. VIN being the unique identifier. The one displayed below is for the highest level SCENARIO. I also have a few measures- No of Cars, Recorded Mileage and Mileage Target.
Now, we should count the number of cars and use each cars mileage divided by its mileage target to assign it to one of the mileage target columns.  I have created a B/C column and used case expression to place the cars to its proper bucket.

So for US-DTG-HAWAII, Recorded Mileage is 27016 divided by Mileage Target of 50,259 = 0.54. This returns the number of cars to the 50-75% column. The rest of the columns are 0. As you can see, it is calculating at the current level.

                      No of Cars   Recorded Mileage   Mileage Target       B/C       Under 25%     25-50%       50-75%      75-100%    Over 100%
US-DTG-HAWAII   |  16,489    |     27,016        |           50,259        |  0.54  |         0         |      0          |    16,489    |     0      |      0
US - NEW                   |    100       |      58,858        |         52,350        |  1.12  |         0         |      0          |        0         |     0      |     100
US - USED           |  16,060    |      22,902        |         48,565        |  0.47  |         0         | 16,060     |         0        |     0      |      0

When we drill down to the lower hierarchies, it will change data depending on where we are. It will only have one is to one value if we are on the lowest level already. So for the lowest level of US-DTG-HAWAII, I will have 16,489 VIN columns. Each of the column will categorize itself to different buckets depending on the B/C value.

                                       No of Cars   Recorded Mileage   Mileage Target       B/C   Under 25%     25-50%       50-75%      75-100%    Over 100%
US-DTG-HAWAII>>>>VIN   |      1       |     23,524        |          42,000        |  0.56 |         0         |      0          |       1          |     0      |      0
US-DTG-HAWAII>>>>VIN   |      1       |     10,996        |          42,000        |  0.26 |         0         |      1          |       0          |     0      |      0
US-DTG-HAWAII>>>>VIN   |      1       |     10,826        |          65,000        |  0.17 |         1         |      0          |       0          |     0      |      0
...
TOTAL                                    |  16,489  |     27,016        |           50,259       |  0.54  |      1891         |      6482    |    4571       |  2162   |      1202

Now, the requirement is even if we are not at the lowest level (VIN level), we need to calculate the buckets column based on the VIN level columns.
So, if we are at the SCENARIO level, this is what we should see:

                      No of Cars   Recorded Mileage   Mileage Target       B/C       Under 25%     25-50%       50-75%      75-100%    Over 100%
US-DTG-HAWAII    |  16,489  |      27,016         |      50,259       |  0.54  |      1891         |      6482    |    4571       |  2162   |      1202

It should sum up all the 1s, in the VIN level. If we are on the second level of the hierarchy, the buckets should sum up the VINs of US-DTG-HAWAII and 2014.

In short, we need to create a measure that calculates the lowest level of the hierarchy but will still change depending on where we are at the hierarchy.

I hope my explanation is clear. Can you help me?