Need help in choosing right Visual for showing Top 10 Accounts by Order count with multiple comments

[SwatKat]

HI , 

I have a scenario where i need to show Top 10 customers by Order count with comments . Comments will be placed on individual orders. I need to show the users in right way , so information can easily be interpreted. Currently I am concatenating all the comments that belong to an Account and showing in the same grid. But this can be done better. Please help if i can design this differently. I am short of ideas

 I have choosen a Table Visual with these columns. Below is how my table visual looks like

AccountName	Order Count	Comments
Account1	975	Comment 1 ;Comment 2; Comment 3
Account2	875	Comment 1 ;Comment 2; Comment 3
Account3	775	Comment 1 ;Comment 2; Comment 3
Account4	675	Comment 1 ;Comment 2; Comment 3
Account5	575	Comment 1 ;Comment 2; Comment 3
Account6	475	Comment 1 ;Comment 2; Comment 3
Account7	375	Comment 1 ;Comment 2; Comment 3
Account8	275	Comment 1 ;Comment 2; Comment 3
Account9	175	Comment 1 ;Comment 2; Comment 3
Account10	75	Comment 1 ;Comment 2; Comment 3

Table Design

Account table
AccountID	Account Name
1	Account1
2	Account2
3	Account3
4	Account4
5	Account5
6	Account6
7	Account7
8	Account8
9	Account9
10	Account10

Order Details
ID	AccountID	Order Quantity	Comments
1	Account1	1	Comment1
2	Account2	1	Comment1
3	Account3	1	Comment1
4	Account1	1	Comment2
5	Account2	1	Comment2
6	Account3	1	Comment2
7	Account1	1	Comment3
8	Account2	1	Comment3
9	Account3	1	Comment3

[v-shex-msft · Accepted solution]

HI @SwatKat,

You can use the following measure formula with 'account' table 'account name' field, 'order detail' table 'comments' field with aggregate mode 'count' to create a table visual show the top 10 accounts and correspond comments:

formula =
VAR summary =
    TOPN (
        10,
        SUMMARIZE (
            ALLSELECTED ( 'Order Details' ),
            'Order Details'[AccountID],
            "CommentCount", COUNT ( 'Order Details'[Comments] )
        ),
        [CommentCount], DESC
    )
VAR currID =
    SELECTEDVALUE ( Account[AccountID] )
RETURN
    IF (
        currID IN SELECTCOLUMNS ( summary, "AccountID", [AccountID] ),
        CONCATENATEX (
            CALCULATETABLE (
                VALUES ( 'Order Details'[Comments] ),
                FILTER ( ALLSELECTED ( 'Order Details' ), [AccountID] = currID )
            ),
            [Comments],
            ","
        )
    )

Regards,
Xiaoxin Sheng