Need help with the descending sort for this DAX values

[abhishek_2593]

Hi, 
I have the following DAX to dynamically select the TOP N values for % of followers from each city but unable to define the sort by descending in this DAX.
What changes can i make to define this descending sort?

Here's the DAX

Top Cities KPI =

VAR N = SELECTEDVALUE('Top'[top])

VAR CityTable =

    SUMMARIZE(

        'Table (2)',

        'Table (2)'[City],

        "TotalFAN", COUNT('Table (2)'[FAN ID])

    )

VAR TotalFAN = CALCULATE(COUNT('Table (2)'[FAN ID]))

VAR TopCitiesTable =

    TOPN(N, CityTable, [TotalFAN], DESC)

VAR ResultTable =

    ADDCOLUMNS(

        TopCitiesTable,

        "Percentage", FORMAT(DIVIDE([TotalFAN], TotalFAN), "0.0%")

    )

VAR ResultString =

    CONCATENATEX(

        ResultTable,

        [Percentage] & " in " & [City],

        ", "

    )

RETURN

    ResultString

3. use as a silcer the table you created for select top N values.

The sorting should be in descending order.

Thank you.

[OwenAuger · Accepted solution]

Hi @abhishek_2593 

To fix the sorting issue, add two arguments to CONCATENATEX:

VAR ResultString =
    CONCATENATEX(
        ResultTable,
        [Percentage] & " in " & [City],
        ", ",
        [Percentage], DESC
    )

A possible alternative version of the measure consolidating some steps:

Top Cities KPI =
VAR N = SELECTEDVALUE ( 'Top'[top] )
VAR TotalFAN =
    COUNT ( 'Table (2)'[FAN ID] )
VAR CityPercentage =
    SUMMARIZECOLUMNS (
        'Table (2)'[City],
        "Percentage", DIVIDE ( COUNT ( 'Table (2)'[FAN ID] ), TotalFan )
    )
VAR TopCityPercentage =
    TOPN ( N, CityPercentage, [Percentage], DESC )
VAR ResultString =
    CONCATENATEX (
        TopCityPercentage,
        FORMAT ( [Percentage], "0.0%" ) & " in " & 'Table (2)'[City],
        ", ",
        [Percentage], DESC
    )
RETURN
    ResultString

[mdaatifraza5556 · Accepted solution]

Hi @abhishek_2593 

Top Cities KPI =
VAR N = SELECTEDVALUE('Top'[top])
VAR CityTable =
SUMMARIZE(
'Table (2)',
'Table (2)'[City],
"TotalFAN", COUNT('Table (2)'[FAN ID])
)

VAR TotalFAN =
CALCULATE(COUNT('Table (2)'[FAN ID]))

VAR TopCitiesTable =
TOPN(N, CityTable, [TotalFAN], DESC)

-- Add both numeric and formatted percentage
VAR ResultTable =
ADDCOLUMNS(
TopCitiesTable,
"NumericPercentage", DIVIDE([TotalFAN], TotalFAN),
"FormattedPercentage", FORMAT(DIVIDE([TotalFAN], TotalFAN), "0.0%")
)

-- Use line break with UNICHAR(10)
VAR ResultString =
CONCATENATEX(
ResultTable,
[FormattedPercentage] & " in " & [City],
UNICHAR(10),
[NumericPercentage], DESC
)

RETURN
ResultString



If this answers your questions, kindly accept it as a solution and give kudos.

[OwenAuger · Accepted solution]

Hi @abhishek_2593 

To fix the sorting issue, add two arguments to CONCATENATEX:

VAR ResultString =
    CONCATENATEX(
        ResultTable,
        [Percentage] & " in " & [City],
        ", ",
        [Percentage], DESC
    )

A possible alternative version of the measure consolidating some steps:

Top Cities KPI =
VAR N = SELECTEDVALUE ( 'Top'[top] )
VAR TotalFAN =
    COUNT ( 'Table (2)'[FAN ID] )
VAR CityPercentage =
    SUMMARIZECOLUMNS (
        'Table (2)'[City],
        "Percentage", DIVIDE ( COUNT ( 'Table (2)'[FAN ID] ), TotalFan )
    )
VAR TopCityPercentage =
    TOPN ( N, CityPercentage, [Percentage], DESC )
VAR ResultString =
    CONCATENATEX (
        TopCityPercentage,
        FORMAT ( [Percentage], "0.0%" ) & " in " & 'Table (2)'[City],
        ", ",
        [Percentage], DESC
    )
RETURN
    ResultString