Sum. Group by, and finally Choice

[Carlo1975]

Hi, I have this table with the Italian Region,

Region	Importo
N/A	6,808,553
Abruzzo	216,626,214
Basilicata	30,334,245
Calabria	67,741,318
Campania	232,525,920
Emilia-Romagna	494,692,584
Estero	39,915,554
Friuli-Venezia Giulia	23,658,685
Lazio	880,851,460
Liguria	327,301,815
Lombardia	1,316,238,858
Marche	1,314,458,160
Molise	15,655,808
Piemonte	252,795,930
Puglia	125,946,018
Sardegna	45,560,611
Sicilia	267,007,610
Toscana	684,136,391
Trentino-Alto Adige	43,808,624
Umbria	208,528,281
Valle D'Aosta	3,460,230
Veneto	255,690,584

The first row is N/A but I cannot have N/A value in my table. So, I have to create a new table taking off the N/A Value. But the final total amount cannot change and so, I have to sum the amount of N/A value to the Region that have the max amount (in my case is Lombardia but in the future can change). I don't know if the max value will be in the future Lombardia or an other one.

So, First: I have to find the region with the max amount.
Second: I have to sum the N/A amount to the value described above.
Finally: I have to cut the N/A Row fron the new table.

thank you very much

Carlo

[Anonymous · Accepted solution]

// Let T be the name of our table.

[New Table] = // calculated table, not a measure
var __NAImporto =
    CALCULATE(
        SELECTEDVALUE( T[Importo] ),
        T[Region] = "n/a"
    )
var __nonNAMaxRegionRow = 
    CALCULATETABLE(
        TOPN(1,
            T,
            // order by Import first
            T[Importo],
            DESC,
            // the by region if there
            // is more than 1 region
            // with the same Importo
            T[Region],
            ASC
        ),
        T[Region] <> "n/a"
    )
var __nonNAMaxRegionImporto =
    CALCULATE(
        SELECTEDVALUE( T[Importo] ),
        __nonNAMaxRegionRow
    )
var __nonNAMaxRegion =
    CALCULATE(
        SELECTEDVALUE( T[Region] ),
        __nonNAMaxRegionRow
    )
var __sum = __nonNAMaxRegionImporto + __NAImporto
RETURN
    UNION(
        FILTER(
            T,
            NOT T[Region] IN {
                "n/a",
                __nonNAMaxRegion
            }
        ),
        {
            (__nonNAMaxRegion, __sum)
        }
    )