Thanks for attaching the data, very helpful in understanding the basic premise.The data shown is only the new registrations made in each year. You want to understand the total used cars in each year, which will include the new car counts from previous year too.
You need to use running totals and achieve this.
Before going there, the structure of your data is not convinient for the same task.So, we pivot this data for ease of calculation.
- In the data connection window, select (shift click) all year number columns.
- Right click shows the options as shown above.
- You can rename the resultant Pivot Field Names and Pivot Field Values into something relevant such as Year and New Cars.
Now this shows exactly what you see in the excel.
Now change your aggregation to a running_sum calculation and you can see the overall car count on each year.
Hope this helps.
Thank you Eevan for your quickly reply.
This is helpful,but my issue is a bit more complicated than that.
Besides the running total,I want to create different groups of cars age (ex.1 years old,2,3, up to 4 years old).so that the end result should show me pro each year,not only the new car,but also (and most important)the number of used cars splitted into the 4 categories(1 year,2,3and 4 years).
This looks really challenging to me and I don't know how to go further...
Ottieni Outlook per iOS<https://aka.ms/o0ukef>