Conditional Average

[#1]

Hello Every one,

Happy New Year!!

1. I have  business like Rates, Credits, Commodities.

2. Always one client Selected

Get average of sales value for each business for all clients, I was able to get this.

Now the problem is I wont get average of all clients for each business meets the threshold.  I tried formula

but did not work, still gives me total average only.

My chart has one dimension that is Business

Here is My formula to get average

=Sum({<Year={$(=MAX(Year))},

       ID={"=((Sum({<Year={$(=MAX(Year))},ID=>}Sales)/1000 )>=$(=only(ThresholdValue)))"}>} Sales)

       /

Count({<Year={$(=MAX(Year))},

       ID={"=((Sum({<Year={$(=MAX(Year))},ID=>}Sales)/1000 )>=$(=only(ThresholdValue)))"}>} Distinct ID)

in above formula

1. ID={"=((Sum({<Year={$(=MAX(Year))},ID=>}Sales)/1000 )>=$(=only(ThresholdValue)))"} will give me all ID whose value greater then threshold, (indirect set)but it is not working.

2. ThresholdValue is the drop down select

I am not sure what am i missing here. Above expression still gives me all clients ave for each business rather then threshold limit

Any idea please?

Thank you in advance

Dev

[#2]

The problem with this is it is not considering the Dimension in expression, I believe in Indirect set analysis. If I select My dimension then it gives correct result. Some how force the dimension into indirect set analysis.

[#3]

Any help please?