Ok, I'm not going to pretend I understand your aggregation... but I think this 'fix' gets you the same numbers, without the overlapping text.
I took COMMITTEE_XSEQUENCE out of the Level of Detail (it is why you're getting the overlapping data, since it's showing the same value for all the items in that column).
Then, that 'broke' the calculation, as it was now summing across those fields, so I went into your:
- Aspire calc
IIF (FIRST()==0, WINDOW_SUM(SUM([Aspire Giving])),0)
IIF (FIRST()==0, WINDOW_SUM(avg([Aspire Giving])),0)
- Limetime calc
IIF (FIRST()==0, WINDOW_SUM(SUM([ENTITY_LIFETIME_COMMIT_AMT])),0)
IIF (FIRST()==0, WINDOW_SUM(avg([ENTITY_LIFETIME_COMMIT_AMT])),0)
It gets the same numbers as you were, without the overlapping, I think it's because you have the same data in each row in the original version when you had the level of detail in, so the 'Average' sorts it out for you.
Although, like I said, I'm not sure of what your calculations are doing entirely!
Dup $$ (5.1.12).twbx.zip 2.8 MB
Thank you!!! I was definately overcomplicating and you cleared it up for me!