Would it be possible to attach an excel file with some dummy data and maybe whats the result you are getting vs expected. That would greatly help in addressing the issue.
Just an FYI,
Count(DISTINCT Chassis) as RolledUpChassisCount,
The above line in the first table will always be 1 as you are grouping by Chassis as well. So not sure why you are using that. Its equivalent to
1 as RolledUpChassisCount