I have a table which contains for each date, for each site, a list of machines identified by their manufacturer, their type, their style and machine ID and a sales number they acrew each day.
I want to calculate a distinct count of these machines based on their Machine ID. which is distinct to each site but can be duplicated across the sites. So both site and machine ID columns act as the primary key but i want to be able to group the figures up to the different levels e.g. Manufacturer, type and style.
Based on the filters on the page. E.g. it could be filtered to all sites and 1 calendar year, I want the distinct count to summarize this range and change if these filters change e.g. I look at one site, it will show the breakdown of count of machines for each manufacturer for the site ONLY or I want to look at a specific manufacturer and type but only over 6 months. I want the count of distinct Machine IDs for the manufacturer over that date range.
I basically want to be able to view the total machines across the estate at different breakdowns and different periods dynamically. Can anyone help? As when i try to average, sum or count distinct it does it on a day level and broken down by the subtypes(type and style) not the total amount of distinct machines for that manufacturer.
