Converting {$} AGGR to {1} - stuck on dimensions side of the formula

[tinkerz1]

Hi,

The following  formula works and I have uploaded a QVW and this table is on the last row of the sheet.

=count(aggr(min({$<[Business Area]=,Category=,Desk=,[Sub Category]=,[Excess]={"=$(=Min([Excess]))"}>}Excess),[Start of Month],[Business Area],Category,[Sub Category],Desk))

How can I convert the above formula to {1} 'All'?

The problem I have is changing the dimensions in AGGR to {1} efficently  and if I need the [Excess]={"=$(=Min([Excess]))"}>} expression

In the upload I have written code in the load script which works and this is the best solution, but I would like to know for future use how to use set analysis in a AGGR formula.

Thanks

[sunny_talwar]

2, 3, 2 is based on selection in Business Area, Do you want to always see 'Premium Sales'?

[tinkerz1]

Yes, I would like a fixed set on 'Premium Sales'

[sunny_talwar]

Try this:

=Count({1<[Business Area]= {'Premium Sales'},Category=,Desk=,[Sub Category]=,[Excess]={"=$(=Min([Excess]))"}>}Aggr(Min({1<[Business Area]= {'Premium Sales'},Category=,Desk=,[Sub Category]=,[Excess]={"=$(=Min([Excess]))"}>}Excess),[Start of Month],[Business Area],Category,[Sub Category],Desk))

or this

=Count({1<[Business Area]= {'Premium Sales'},Category=,Desk=,[Sub Category]=,[Excess]={"=$(=Min([Excess]))"}>}Aggr(Min({1<[Business Area]= {'Premium Sales'},Category=,Desk=,[Sub Category]=,[Excess]={"=$(=Min({1}[Excess]))"}>}Excess),[Start of Month],[Business Area],Category,[Sub Category],Desk))