I have a table that has millions of records in it. The important fields here are loss amounts, by policy, year, cause of loss, and location. It's linked to another table that has information for each policy - such as department, underwriter, etc.
I need to show expected losses by year at certain levels. I have it working for aggregates with the following:
A calculated dimension with the formula =Aggr(Rank(sum(Loss_Amt),4,1), Year)
A calculated expression with the formula Top(aggr(sum(Loss_Amt), Year),500) which will show the 500th worst year.
Don't worry about the formulas themselves, they're doing what they're supposed to do, it's tough to explain why they are what they are. Anyway, I want to be able to add in another dimension - let's say Department - and have it show that 500th worst year for each department. But when I add in that dimension, everything else goes blank. Any ideas as to why?
I will try and get together a sample file. The way it's set up now is complex (I have other tables also), but I'm trying to shrink it down with some sample data.
