combining data

Question

Hi,&nbsp;I have a formula to combine 2 tablesTable 1.Date &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Category &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Additional Category &nbsp; &nbsp; &nbsp; Qty1-Jan-17 &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Add-1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 101-Jan-17 &nbsp; &nbsp; &nbsp; B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;72-Jan-17 &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;Add-1 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;22-Jan-17 &nbsp; &nbsp; &nbsp; A &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 153-Jan-17 &nbsp; &nbsp; &nbsp; C &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp;Table 2 combines Table 1 &amp; converting Additional Category = "Add-1" entries to Category Cthe formula isUNION (SELECTCOLUMNS( FILTER(Table1,Table1[Additional Category] = "Add-1"),"Date",Table1[Date],"Category",SUBSTITUTE(Table1[Category],"A","C"),"Qty",Table1[Qty]))&nbsp;Thus, the result I expected isCategory &nbsp; &nbsp; &nbsp; QtyA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27 (10+2+15)B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7C &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;29 (10+2+4, which comes from Qty from Category = C &amp; Additional Category = Add-1)&nbsp;However, what I got isCategory &nbsp; &nbsp; &nbsp; QtyA &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;27 (10+2+15)B &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7C &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4It seems that the Qty from Additional Category = Add-1 where the Category has been changed from "A" to "C" is not&nbsp;included in the Category C.&nbsp;Is there anything wrong in my formula?Need advise. Thanks.

v-jiascu-msft · Accepted Answer

Hi&nbsp;RMV,&nbsp;It seems your formula is incomplete. I created a new one. Would you like to try it?&nbsp;Table 2 =
SUMMARIZE (
    'Table1',
    Table1[Category],
    "Value", IF (
        'Table1'[Category] = "C",
        CALCULATE (
            SUM ( Table1[QTY] ),
            FILTER (
                ALL ( 'Table1' ),
                'Table1'[Category] = "C"
                    || 'Table1'[Additional Category] = "Add-1"
            )
        ),
        SUM ( Table1[QTY] )
    )
)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Best Regards!Dale

Forum Discussion

combining data

2 Replies

Related content

Data Combining Problem

Combining Column Data

Underlying Error Privacy...combining of data

Tables combine

Combine data