Show Only Top 2 Orgs in Separate tables and these should change dynamically as the count changes

[SK28]

Here is the detailed level of data 

Table 1:

ORG	Name	Count of tickts
TCS	A	100
TCS	B	90
TCS	C	80
TCS	D	70
TCS	E	60
CTS	F	40
CTS	G	30
CTS	H	20
CTS	I	10
Wipro	J	8
Wipro	K	7
Infy	L	6
Infy	M	5
Dell	N	4

The required output should be Details of top 2 ORGs in separate tables. One table is TCS and other should be CTS, as the ticket counts are High for TCS as 1st and CTS as 2nd,The output should be change dynamically based on the count of tickets.

Table 1 Output:

Name	Count
A	100
B	90
C	80
D	70
E	60

Table 2 Output:

Name	Count
F	40
G	30
H	20
I	10

For Now we are using in set analysis, we are passing static value as Org = TCS to get the results as Table 1 

and Org = CTS to get the results as Table 2

But in future if the tickets Count changes then it should Show the detail of EMPLOYEE Name info of those ORGs with highest numbers of Tickets

Can you please help me on this?

 If a user is selecting ORG [Rank based on ORG] it's displaying correctly but in the Other table it's displaying as Null,

the Solution should be dynamic is what they're expecting, 

As per the above table,

If the user is selecting ORG = WIPRO, in the detailed output it is showing the details of Wipro,

But in the table 2; They want next Org should be shown Dynamically,  In our case it is showing Null, 

but the expectation is to show the Values of ORG = INFY 

the below solution is partially Correct, Can you please amend the logic

[BrunPierre]

Hi, try these

Output 1: Sum({<ORG = {"=Rank(Sum([Count of tickts]))=1"}>} [Count of tickts])

Output 2: Sum({<ORG = {"=Rank(Sum([Count of tickts]))=2"}>} [Count of tickts])

[SK28]

Thanks @BrunPierre  It's working

[SK28]

Hi @BrunPierre , If a user is selecting ORG [Rank as 3] it's displaying correctly but in the Other table it's displaying as Null,

the Solution should be dynamic is what they're expecting, 

As per the above table,

If the user is selecting ORG = WIPRO, in the detailed output it is showing the details of Wipro 

But in the table 2; They want next Org Dynamically, In our case it is showing Null, 

but the expectation is to show the Values of ORG = INFY 

[Tanish · Accepted solution]

Hi @SK28 ,

Try this Out

t:

LOAD

    ORG,

Sum("Count of tickts") as No_of_Ticket

FROM [lib://DataFiles/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1)

group by ORG;

t2:

Load ORG , RowNo() as Rank;

Load

ORG,

No_of_Ticket

Resident t

Order by No_of_Ticket asc ;

Drop Table t;

t3:

LOAD

    ORG,

    Name,

    "Count of tickts"

FROM [lib://DataFiles/Book1.xlsx]

(ooxml, embedded labels, table is Sheet1);

In the front end-

Use this Expression 

Table1 - Sum({<Rank = {$(=Max(Rank))}>}[Count of tickts])

Table2 - Sum({<Rank = {$(=Max(Rank)-1)} , ORG=>}[Count of tickts])

This will work dynamically, By default, in the First table it will show Top1 ORG and in the Table 2 it will show Top2nd ORG , if any person select any ORG from the Filter, Then in the first table it will show That ORG then according to the count it will show another one.

Hope this will help...

Thanks

Tanish