Aggr (2 or 3 demens.) + rank +set analys. as selectable

[warfollowmy_ver]

Hello everybody. Please help me, the problem is very important, I really do not understand how osdelat floor.

I'm already broke my head. How to make a selectable object using the functions Aggr (2 or 3 demensions) + rank + set analysis.

Purpose - ABC analysis as this Recipe for an ABC Analysis. hic But i need to choose. I have several stores and catalogs. And in stores and catalogs need calculate ABC throuhge rank and count (rank / count ).

In my working document, this formula gives the correct grade, but when choosing a particular product becomes rank  = 0.

=      Rank(

                 Aggr(

                         sum({<[Product]=, [Quantity]-={0},[Price]-={0},[Involved in ABC]={Yes}>} [Quantity]*[Price])

               , [Product], Shop)

      ,1)-1

For example in "count" all without problems:

=     aggr(

               Count ({<[Product]=, [Quantity]-={0},[Price]-={0},[Involved in ABC]={Yes}>} DISTINCT TOTAL <Shop, Catalog> [Product])

     ,Shop, Product, Catalog)

How to do it?

As an easy example, I put a test document, there is need to do so only on store (shop) - last column.

[sunny_talwar]

May be try this:

=If(Sum(Sales) > 0, Rank((Sum({1}Sales)),0,1))

[sunny_talwar]

For your actual scenario, may be try this:

If(Sum({<[Product]=, [Quantity]-={0},[Price]-={0},[Involved in ABC]={Yes}>} [Quantity]*[Price]) > 0,

Rank(

     Only({1<[Product]=, [Quantity]-={0},[Price]-={0},[Involved in ABC]={Yes}>}

          Aggr(

               Sum({1<[Product]=, [Quantity]-={0},[Price]-={0},[Involved in ABC]={Yes}>} [Quantity]*[Price]),

          [Product], Shop)),

1) - 1)

[warfollowmy_ver]

OMG thank you very much! You really helped! I will understand how it works!

P.S.

I think myself, but I think you can do it faster, please, I have added a catalog. We need two columns - the rank on store and the rank on store-within-directory. Thank you in advance! Invested doc with catalog.

Even when you add the directory as a measurement of the rank changes (scrin).

In order to understand that I want to put Excel file. That is a result of two columns - one with the rank of the store and the second store and catalog that for any choice of the rank has not changed.

[warfollowmy_ver]

stalwar1 You have helped, but this solution is not suitable if a few dem, you could not tell yet, please?

[sunny_talwar]

I will take a look at this today