How to order by column name and measure value.

[Anonymous]

I have to display top N sum based on slicer value and sort them by code Group Ascending and in that group sort by sum values.

e.g N=2

Sample data:

sales=Sum(amount)

Code Group |Name | Sales

B|B1|21

B|B2|51

B|B3|41

A|A1|20

A|A2|50

A|A3|40

C|C1|15

C|C2|25

output:

A|A2|50

A|A3|40

B|B2|51

B|B3|41

C|C2|25

C|C1|15

[v-diye-msft]

Hi @Anonymous 

Add below calculated column, then filter the rank less than or equal to:

Rank = RANKX(FILTER('Table (2)',[Code Group]=EARLIER('Table (2)'[Code Group])),[Sales],,DESC)

[Anonymous]

For 'C' rank is showing correctly but order is not correct it should be like first 25 and then 15

[v-diye-msft · Accepted solution]

Hi @Anonymous 

You can add a new measure below to sort the column:

Measure 4 = 
VAR t =
    SUMMARIZE (
        ALLSELECTED ( 'Table (2)' ),
        'Table (2)'[Code Group],
        'Table (2)'[Name],
        'Table (2)'[Rank],'Table (2)'[Sales]
    )
RETURN
    COUNTROWS (
        FILTER (
            t,
            ISONORAFTER (
                    'Table (2)'[Code Group], SELECTEDVALUE ( 'Table (2)'[Code Group]), DESC,
'Table (2)'[Sales],SELECTEDVALUE('Table (2)'[Sales]),ASC
            )
        )
    )

[Anonymous]

You solution works fine but our client don't want to see this extra column in export. I know how to hide it at visual level but we can't hide it in export.