- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Grouping the Dimension Values Based on Metric Value in Straight table
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 |
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@polisetti Is the Rank is your dimension or measure?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Kushal_Chawda Rank is the Measure calculated based on Count of Accounts.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Kushal_Chawda I think this is working. I need to change the things based on my requirement.
Thank you
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@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])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@sunny_talwar I thought it will result null but @Kushal_Chawda logic worked. Thanks again