Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating a residual.

Hi,

 

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

 

MarketSales
Total Market100
Customer A75
Banner A120
Banner A230
Banner A325
Customer B25
Banner B110
Banner B25
Banner B35

 

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

 

MarketSales
Total Market100
Customer A75
Banner A120
Banner A230
Banner A325
Customer B25
Banner B110
Banner B25
Banner B35
Banner B4 (other)5

 

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

 

Br Kent

Need Help

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    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 )