Grouping the Dimension Values Based on Metric Value in Straight table

[polisetti]

Hi Team,

Could you please help me in grouping the dimension values in the Straight table. Below is the table that I have and I need Sales Rep concat if the Count metric is the same. The expected result is also shown below

Rank	Sales Rep	Count
1	Rep1	10
2	Rep2	9
3	Rep3	8
4	Rep4	8
4	Rep5	7
5	Rep6	7
6	Rep7	7
5	Rep8,	6
6	Rep9	6
7	Rep10	6
8	Rep15	6

Expected Result:

Rank	Sales Rep	Count
1	Rep1	10
2	Rep2	9
3	Rep3, Rep4	8
4	Rep5, Rep6, Rep7	7
5	Rep8, Rep9, Rep10, Rep15	6

[Kushal_Chawda]

@polisetti  Is the Rank is your dimension or measure?

[polisetti]

@Kushal_Chawda Rank is the Measure calculated based on Count of Accounts. 

[Kushal_Chawda · Accepted solution]

@polisetti  You need to swap the position of dimension and measure

Dimension:
=aggr(sum(Count),[Sales Rep])
//Replace sum(Count) with your actual count expression

Measures:

1) Sales Rep
=Concat([Sales Rep],',')

2) Rank
=RowNo(Total)

Note:

Make a sort order of count measure  as descending and promote it to first.

[polisetti]

@Kushal_Chawda On the dimension - to replace the expression. The actual count expression contains set analysis like below which will result to null.

=aggr(Count(Distinct{<[Source]={'Sales'},[Date]={">$(=Date(Today()-90))<=$(=Date(Today()))"}>} AccountId),[Rep Name])

How to do that?

[polisetti]

@Kushal_Chawda I think this is working. I need to change the things based on my requirement. 

Thank you

[sunny_talwar]

@polisetti the calculated dimension you posted resulted in null? Or do you just think it will result in null? I think all you need is to add Rank() function to get a rank here

=Aggr(
  Rank(Count(Distinct{<[Source]={'Sales'},[Date]={">$(=Date(Today()-90))<=$(=Date(Today()))"}>} AccountId))
, [Rep Name])

[polisetti]

@sunny_talwar I thought it will result null but @Kushal_Chawda  logic worked. Thanks again