Calculating a residual.

Anonymous

Try this calculated Table

From the Modelling Tab>>New Table

Calculated Table =
VAR AddColumn =
    ADDCOLUMNS (
        Table1,
        "Missing Sales",
        VAR myCustomer =
            IF ( SEARCH ( "Customer", [Market], 1, 0 ) > 0, RIGHT ( [Market], 1 ) )
        VAR mysales =
            CALCULATE (
                SUM ( Table1[Sales] ),
                FILTER ( Table1, SEARCH ( "Banner " & myCustomer, Table1[Market], 1, 0 ) > 0 )
            )
        VAR MaxBanner =
            CALCULATE (
                MAX ( Table1[Market] ),
                FILTER ( Table1, SEARCH ( "Banner " & myCustomer, Table1[Market], 1, 0 ) > 0 )
            )
        RETURN
            IF (
                SEARCH ( "Customer", [Market], 1, 0 ) > 0
                    && ( [Sales] - mysales )
                    > 0,
                "Banner " & myCustomer
                    & RIGHT ( MaxBanner, 1 ) + 1
                    & " (other)"
                    & "|"
                    & [Sales] - mysales
            )
    )
VAR desiredrows =
    FILTER (
        SELECTCOLUMNS (
            AddColumn,
            "Market", PATHITEM ( [Missing Sales], 1 ),
            "Sales", PATHITEM ( [Missing Sales], 2, 1 )
        ),
        [Sales] > 0
    )
RETURN
    UNION ( desiredrows, Table1 )