Create visual with column name of top n values

[Perberos]

Hi, everyone. I need some help.

I have a database called "Base" with some columns:
1 - ID
2 - Company: Company name 
3 - Origin: List of origin cities
4 - Destination: List of destination cities
5 - Amount(kg): Amount of Kg transported

(Example - Base database)

I want to create a table as follows:

• In the first column, it should contain the names of the companies.
• The second column should be called "Top 1" and should specify the name of the destination with the highest sum of transported Kg
• The third column, called "Top 2," should specify the name of the destination with the SECOND highest sum of transported Kg.
• The fourth, fifth, and so on should follow the same logic.

(Desired visual - Example)

I have tried the code below, but without success.

Destino Mais Utilizado = 
VAR DestinoMaisUtilizado =
    TOPN(1,
        SUMMARIZE('Base', 'Base'[Destination], "Total KG Carga Paga", SUM('Base'[Amount (kg)])),[Total KG Carga Paga], DESC)

RETURN
    SELECTCOLUMNS(DestinoMaisUtilizado, "Destino Mais Utilizado", 'Base'[Destination])

Thanks!

[Jihwan_Kim · Accepted solution]

Hi, I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Destination topN table = 
VAR _rowcount =
    ADDCOLUMNS (
        VALUES ( Base[Company] ),
        "@rowcount", CALCULATE ( COUNTROWS ( Base ) )
    )
VAR _maxrowcount =
    MAXX ( _rowcount, [@rowcount] )
VAR _createNtable =
    ADDCOLUMNS (
        ADDCOLUMNS ( GENERATESERIES ( 1, _maxrowcount, 1 ), "@Top", "Top " ),
        "@metricsname", [@Top] & [Value]
    )
VAR _addcompanyname =
    GENERATE ( Base, _createNtable )
VAR _addresultcolumn =
    FILTER (
        ADDCOLUMNS (
            _addcompanyname,
            "@result",
                CALCULATE (
                    SUM ( Base[Amount (kg)] ),
                    WINDOW (
                        [Value],
                        ABS,
                        [Value],
                        ABS,
                        FILTER ( Base, Base[Company] = EARLIER ( Base[Company] ) ),
                        ORDERBY ( CALCULATE ( SUM ( Base[Amount (kg)] ) ), DESC ),
                        ,
                        ,
                        MATCHBY ( Base[ID] )
                    )
                )
        ),
        Base[Amount (kg)] = [@result]
    )
RETURN
    SUMMARIZE (
        _addresultcolumn,
        Base[ID],
        Base[Company],
        Base[Destination],
        [@metricsname],
        [@result]
    )