topn and concatenatex

[chock974]

I have table with item1, acts and coefficients.

I want to concatenate the acts and coeff by item1 and bring out the top 5 of the most used (acts+coeff).

example :

expected result :

Thx

[Anonymous · Accepted solution]

Hi @chock974 ,

I create a table as you mentioned.

Next I create a calculated column and a measure.

Concatenated = 'Table'[ACTE] & " " & 'Table'[COEFF]

Measure = 
CALCULATE (
    COUNTROWS ( 'Table' ),
    ALLEXCEPT ( 'Table', 'Table'[Concatenated] )
)

Then I create two new tables and it will give you what you want.

GroupedTable =
SUMMARIZE (
    'Table',
    'Table'[ITEM1],
    "ConcatenatedActs", CONCATENATEX ( 'Table', 'Table'[Concatenated], ", " )
)

Top5Concatenations =
TOPN (
    5,
    SUMMARIZE ( 'Table', 'Table'[Concatenated], "Count", 'Table'[Measure] ),
    [Count], DESC
)

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[chock974]

Almost a good solution

i try our idea with table and i have got the solution

and i try to do the same thing only in measure and the result is wrong.

it's my pbix to upload here : TOPN and CONCAT BY MEASURE 

[chock974]

ok

https://drive.google.com/file/d/1qkLv8KNNhJyrGZhoQNvBOLYQ7RF44DFA/view?usp=drive_link

hope it's correct!!!

[bhanu_gautam]

@chock974 , Can you share data in a format that can be copied instead of picture