Calculating a residual.

[Anonymous]

Hi,

I have a sales table with sales on different aggregated levels. A simplification is like this:

Market	Sales
Total Market	100
Customer A	75
Banner A1	20
Banner A2	30
Banner A3	25
Customer B	25
Banner B1	10
Banner B2	5
Banner B3	5

So the table holds both Total sales (per sku, per period ect) and sales per customer and per banner (sub customer). When summing banners for Customer B (Banner B1 to B3) they do not sum up to total Customer B - there is a residual. So - I want to calculate the residual and would like to have it on the same table as a "Banner B4 (other)"

Market	Sales
Total Market	100
Customer A	75
Banner A1	20
Banner A2	30
Banner A3	25
Customer B	25
Banner B1	10
Banner B2	5
Banner B3	5
Banner B4 (other)	5

But I have no idea how to approach that - any guidance in the right direction would be appreciated...!

Br Kent

[Zubair_Muhammad]

@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 )

[Zubair_Muhammad · Accepted solution]

@Anonymous

Please see attached file as well

[Anonymous]

PERFECT - Thank You very much!