Use Concatenatex and topn to create a string containing the values of column 1, ordered by column 2

[Singularity9]

Hello. I have a table, Test, with two columns:

Locality	Percentage
A	1
B	2
C	3
D	4
E	-1
F	-2

I would like to create a measure which lists the positive Localities only in a single string, ordered by Percentage (descending). So in this case it would run "D, C, B and A". I'm using concatenatex and topn to do this.

This is the code I'm using: 

calculate(
Concatenatex(topn(
COUNTROWS(distinct(Test[Locality]))-1, test, Test[Percentage], Desc),Test[Locality], ", ")
&" and "
&Concatenatex(topn(1, test, Test[Percentage], asc),Test[Locality]),
Test[Percentage]>0)

It works almost perfectly, but it doesn't give me the answers in order, since TopN doesn't do that. So instead of "D, C, B and A" I get something like "C,B, A and D" - a completely random order. My question is, how can I make sure the Values are concatenated in order? Thanks in advance.

[DataInsights · Accepted solution]

@Singularity9,

Try using the CONCATENATEX parameters OrderBy_Expression and Order:

Concatenate Measure = CALCULATE (
    CONCATENATEX (
        TOPN (
            COUNTROWS ( DISTINCT ( Test[Locality] ) ) - 1,
            test,
            Test[Percentage], DESC
        ),
        Test[Locality],
        ", ",
        Test[Percentage],
        DESC
    ) & " and "
        & CONCATENATEX ( TOPN ( 1, test, Test[Percentage], ASC ), Test[Locality] ),
    Test[Percentage] > 0
)