Sort by another column - how to put group "others" always on the bottom in Matrix

[mbylicki]

Hello Community,

my data set looks like below:

Brand	Year	Registrations
Audi	2023	1
BMW	2023	2
VW	2023	3
Audi	2022	1
BMW	2022	2
VW	2022	3
Dacia	2022	4
Toyota	2021	1
Dacia	2021	2
VW	2021	3

My goal is to show in Matrix TOP10 Brands in selected Year (separate slicer) and all other Brand show as "Other", but users want to see it always at the bottom of the list (no matter number of registrations)

I already have Measure for ranking: 

Brand Rank = RANKX(ALLSELECTED('Reg Ranking for Top Each Year'[Brand]), CALCULATE([registrations], ALLEXCEPT('Reg Ranking for Top Each Year', 'Reg Ranking for Top Each Year'[Brand]), 'Reg Ranking for Top Each Year'[Year] = SELECTEDVALUE('Reg Ranking for Top Each Year'[Year] )),,DESC,Skip)

and calculated column:

Brand Group = IF([Brand Rank] <= 10, 'Reg Ranking for Top Each Year'[Brand], "OTHER")

Do you have any idea how to sort Brand Group column to achieve my idea?

 it should looks like this.

I tried sort the Brand column by another but PBI says "There can't be more than one value in 'Custom Sort' for the same value in 'Brand group'

Thanks in advance for your support.

Maciej

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

One of ways to create a sort order is to create a new measure, and put the measure into the tooltip. And then, sort it by the measure.
I hope the below can provide some ideas on how to create a solution for your datamodel.

Please check the below pictures and the attached pbix file in the below.

1. Create Brand dimension table, and relate to the fact table.

Brand Dimension = 
UNION ( DISTINCT ( 'Data Fact'[Brand] ), { "Others" } )

2. create a measure that shows top3 and others.

Top 3 measure: =
VAR _topthreelist =
    SUMMARIZE (
        TOPN (
            3,
            ALL ( 'Brand Dimension'[Brand] ),
            CALCULATE ( SUM ( 'Data Fact'[Registrations] ) ), DESC
        ),
        'Brand Dimension'[Brand]
    )
VAR _topthreevaluetotal =
    CALCULATE ( SUM ( 'Data Fact'[Registrations] ), _topthreelist )
VAR _othersvaluetotal =
    CALCULATE (
        SUM ( 'Data Fact'[Registrations] ),
        ALL ( 'Brand Dimension'[Brand] )
    ) - _topthreevaluetotal
RETURN
    SWITCH (
        TRUE (),
        SELECTEDVALUE ( 'Brand Dimension'[Brand] ) = "Others", _othersvaluetotal,
        NOT ( HASONEVALUE ( 'Brand Dimension'[Brand] ) ), SUM ( 'Data Fact'[Registrations] ),
        CALCULATE ( SUM ( 'Data Fact'[Registrations] ), KEEPFILTERS ( _topthreelist ) )
    )

3. create a ranking measure for soring and put it into the tooltip.

ranking for sorting = 
IF (
    ISBLANK ( [Top 3 measure:] ),
    BLANK (),
    RANKX (
        ALL ( 'Brand Dimension'[Brand] ),
        CALCULATE ( SUM ( 'Data Fact'[Registrations] ) ),
        ,
        DESC
    )
)

4. Sort by ranking for sorting measure