Many thanks Saurabh. I can see how this method works. I would like this table to interact with tables on my report which look at the detail behind the sales. How can i do this? Would i need to create a relationship on Final output to table 1?
Sorry another question! I cant add the relationship because the FINAL table contains a blank row (null values). Im not sure why this is, possibly because some sales have no results in a year. How do i force the line to be removed. Is it using the query editor? I cant see it in my list of tables when i go into query editor.
Thanks Saruba. I now have another problem, as the source data has increased in rows -- it is corrupting some of my other data tables where there is a count/sum on other columns. Therfore im not sure i can use this solution. Also this source data links to an excel file and when i refresh, Im not sure how this would work? Any advuce gratefully recieved!
I have used the same kind of formula..that too for .txt file i.e. with 3-4 million rows.
It works perfectly fine.
As far as the error is concerned I think it is because of the relationship between two tables.
Please check if one of the columns always have unique values & the relationship between tables does create a circular dependency (Can be solved in changing the join type i.e. "both" or "single" in some cases.)