CALCULATE VALUE FROM RANK SELECTION

[philgood34]

Hi

I want to calculate the quota of the TOP FIVE sectors for the margin indicator in a KPI

Dimension SECTORS

Measures  : SALES

                    COST

and MARGIN = SALES - COST

so in my KPI i 'm waiting for

                                                       SUM( top five (SALES-COST)) / SUM (TOTAL (SALES - COST))

Can you help me to find the right syntax using RANK ?

thank's in advance

Philippe

[its_anandrjs]

Try with Rank function for finding top 5 Margin Sales

=if( Rank(SUM (SALES-COST) ) <= 5, SUM (SALES-COST) )

  /

  SUM (TOTAL (SALES - COST))

[philgood34]

Hi Anand

Thank's for your answer, but unfortunetly, the result is 100 % (i tested in 2 of my apps )

Regards

Philippe

[philgood34]

as shown below

the SUM(TOTAL(SALES-COST)) is the total for the top five and not the total of the all selection ...

[philgood34 · Accepted solution]

hI

I finaly found a correct syntax without "IF" (i didn't understood why IF without THEN in the Anand suggestion ...)

so i apply

=Sum({<SECTORS_LIB = {"=Rank(Sum(SALES-COST))<=2"}>} (SALES-COST))/Sum(TOTAL (SALES-COST))

(see the result below with few changes)

regards

Philippe

[its_anandrjs]

That's right Phillipe but it is working at my end may be some typo or maybe dimension issue there in your chart kidly check this.

This is the expression

=SUM( {<Dim1 = {"Rank(SUM (SALES-COST) ) <= 5"}>} SALES-COST)

  /

  SUM (TOTAL (SALES - COST))