How are you calculating the Global CAGR? I think it would be best to have a calculated field for Global CAGR using an LOD expression with FIXED as one of the attributes.
As these are still early steps, I've been using the standard CAGR equation similar to the built-in one:
POWER(ZN(SUM([Quantity (kbd)]))/LOOKUP(ZN(SUM([Quantity (kbd)])), FIRST()),ZN(1/(INDEX()-1))) - 1
It seems the LOD expressions don't play well with table calculations though, as I get the error: LOD expressions cannot contain table calculations or ATTR function.
It seems the FIXED attribute would probably solve what I'm trying to do. Any ideas how I can get around this error?
Yes it is correct that the LOD function will have a conflict with Table calculations if are using aggregation like SUM in the formula.
Since you are using First() in the formula for CAGR, it will change depending on the relative position of the individual row with respect to the first row in the table - which is determined by the filters or other selections you make in your table.
Can you post your data? I think it will help people to review and provide suggestions.