# Annual Average Chart **URL:** \ **Category:** Ask for Help\ **Created:** [December 6, 2023, 8:17am UTC](https://community.glideapps.com/t/annual-average-chart/68410 "2023-12-06T08:17:07Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![Cameraville](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/cameraville/32/60231_2.png) [@Cameraville](https://community.glideapps.com/u/Cameraville)\ **Post date:** [December 6, 2023, 8:17am UTC](https://community.glideapps.com/t/annual-average-chart/68410/1 "2023-12-06T08:17:07Z")
I have a table of 300 Things, they each have a multiple relation to a price table (which is around 1300 records of prices these things have sold at). When viewing a Thing, I would like to see a chart showing the average selling price by year. Very simple request. Though I cannot find a viable way to do this. By default a chart will display a sum of sales per year. This has no value to me. I need the average. I’m trying to take small steps to determine the data format requirements to make this work - to help me craft some solution. Ultimately I have determined that, the way the chart component works, I need to have each year as row, so I can select “year” for the X axis. Am I thus far correct? If so, what is next? I fully understand rollups and lookups, but my difficult is in relating each Thing to itself in the Price table. If I have a column in the pricing table called ‘Things’ I can relate to that of course, but each cell will have all 300 things, because each thing sold each year. Where I fail is, when the relationship is made, I can only pull in the average price from all sales of all things that year - I do not know how to get a rollup per thing per year. I have crafted a temporary solution where the chart is behind a button, and the button click has an action which writes the current thing to a helper table and successfully makes the calculations and charts I’m looking for. But this means I can only calculate one things data at a time, and no ‘average price by year’ data exists for all 300 things. I would love to get some ideas - hopefully I am doing this wrong and it is easier than I realise. **If my description is difficult to understand, essentially I would like a chart component where the values displayed are an average and not a sum.** Here is my desired result and current situation, but as I said the data is only now being calculated one Thing at a time which is not the desired backend. ![Screenshot 2023-12-06 at 10.23.06](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/4/3/43791d7a9f3856a07d480f88eecf8909cf7a72dd.jpeg) ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 6, 2023, 8:31am UTC](https://community.glideapps.com/t/annual-average-chart/68410/2 "2023-12-06T08:31:05Z")
> [@Cameraville](#): > > Ultimately I have determined that, the way the chart component works, I need to have each year as row, so I can select “year” for the X axis. Am I thus far correct? Yes. > [@Cameraville](#): > > I have crafted a temporary solution where the chart is behind a button, and the button click has an action which writes the current thing to a helper table and successfully makes the calculations and charts I’m looking for. But this means I can only calculate one things data at a time, and no ‘average price by year’ data exists for all 300 things. I guess my question would be - why is that a problem? It’s more or less what I would do, except that I would write the value as part of the navigation step that gets me to the details screen for each thing. Unless you have a requirement to view charts for multiple things on the same screen - why isn’t this good enough? ---
**Author:** ![Cameraville](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/cameraville/32/60231_2.png) [@Cameraville](https://community.glideapps.com/u/Cameraville)\ **Post date:** [December 6, 2023, 8:48am UTC](https://community.glideapps.com/t/annual-average-chart/68410/3 "2023-12-06T08:48:02Z")
For me it’s a problem because the selected Thing writes to the user profile column and therefore I cannot show these charts to non users. I have done this because I really don’t like the idea of making a user specific column on the Thing table, just to populate 299/300 items with a value that has no relation to that row. Are my only two options to accept what I have now or to write the choice of the selected thing to all unrelated things? Also this is an action on the table of each thing, so I cannot have the whole experience take place on a one-row non-user user-specific helper table. Or perhaps I could, but that also sounds very ugly and illogical… ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 6, 2023, 8:59am UTC](https://community.glideapps.com/t/annual-average-chart/68410/4 "2023-12-06T08:59:10Z")
To make it work for non-signed in users, all you really need to do is move that user specific column to your Helper Table (the table that is the source of the chart). I assume that table has one row per year, yes? Given that, here is what I would suggest: - Add a user specific column to that table to hold a ThingID - In your Things table, create a Single Value → First → Whole row column that targets the Helper Table - When navigating to the details screen of a Thing, use a custom action that first sets the ThingID in that user specific column via the Single Value column, and then does the Show Detail Screen as a second step - In the Helper Table, add a Single Value column that applies that user specific column to all rows - Use that combined with the year in a Query column to relate to the Sales data, then do your rollups through that query ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [December 6, 2023, 2:28pm UTC](https://community.glideapps.com/t/annual-average-chart/68410/5 "2023-12-06T14:28:01Z")
Maybe in the helper table, you can use a JavaScript column that takes the current screen URL, and gives you the screen’s rowID. Saves some updates. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 6, 2023, 2:29pm UTC](https://community.glideapps.com/t/annual-average-chart/68410/6 "2023-12-06T14:29:18Z")
Very clever - never considered that. ---
**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\ **Post date:** [December 14, 2023, 9:24am UTC](https://community.glideapps.com/t/annual-average-chart/68410/7 "2023-12-14T09:24:27Z")
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed. ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [December 14, 2023, 9:27am UTC](https://community.glideapps.com/t/annual-average-chart/68410/8 "2023-12-14T09:27:07Z")