How to select top 3 sales person dynamicly?

[#1]

Hi,

I have a fact table as below:

Month	Custid	Sales
1	A	100
1	B	120
1	C	80
1	D	90
2	A	110
2	B	100
2	C	90
2	D	110

How to show a top 3 person in a gauge dynamicly, in stead of expression of 'sum({<Custid={'B'}>} Sales)','sum({<Custid={'A'}>} Sales)','sum({<Custid={'D'}>} Sales)' in the gauge?

[jagan]

Hi,

Please attached file for solution, used variables and set analysis to achieve.

Regards,

Jagan.

[#3]

I think you can do this by using a calculated dimension so for the top ranked one use

Aggr(if(rank(sum(Sales))=1,Custid),Custid)

[jonathandienst]

Hi

Try this:

For the first gauge:

Title: 

=FirstSortedValue(CustID, -Aggr(Sum(Sales), CustID), 1)

Expression:

=Sum({<CustID = {"$(=FirstSortedValue(CustID, -Aggr(Sum(Sales), CustID), 1))"}>} Sales)

For the second gauge:

Title: 

=FirstSortedValue(CustID, -Aggr(Sum(Sales), CustID), 2)

Expression:

=Sum({<CustID = {"$(=FirstSortedValue(CustID, -Aggr(Sum(Sales), CustID), 2))"}>} Sales)

For the third gauge:

Title: 

=FirstSortedValue(CustID, -Aggr(Sum(Sales), CustID), 3)

Expression:

=Sum({<CustID = {"$(=FirstSortedValue(CustID, -Aggr(Sum(Sales), CustID), 3))"}>} Sales)

HTH

Jonathan

[#5]

Hi,

Please check if attached solution works for you.

[#6]

Thank you very much for everyone.

[#7]

Hi,

If A AND B are both 220, it will not works, any solutions?

[#8]

Hi,

If A AND B are both 220, it will not works, any solutions?

[#9]

Hi,

If A AND B are both 220, it will not works, any solutions?

[#10]

But if A AND B are both 220, it will not works, any solutions?