I have a chart where the user first selects a threshold (variance %) and this should limit the rows that appear in the chart to the ones that meet this limit. I used a calculated dimension with an AGGR function to get this to work and it works beautifully when there is only one dimension. But I need to have more than one dimension, and this expression breaks down, adding extra rows (beyond the threshold) to the chart. How can I correctly adjust this expression to allow for the added dimensions?
This is the working expression with a single dimension:
=aggr(if((sum({<Year={$(=max(Year))}>}m.extrev)/
sum({1<Year={$(=max(Year)-1)}>*<Year=,%_invdate={"<=$(=addmonths(max(%_invdate),-12))"}>}m.extrev))-1<=$(Variance%)
,Brand)
,Brand)
I tried this and got extra rows:
=aggr(if((sum({<Year={$(=max(Year))}>}m.extrev)/
sum({1<Year={$(=max(Year)-1)}>*<Year=,%_invdate={"<=$(=addmonths(max(%_invdate),-12))"}>}m.extrev))-1<=$(Variance%)
,Brand)
,Brand, [Item Type])
Any ideas?
