combining data

[RMV]

Hi,

I have a formula to combine 2 tables

Table 1.

Date           Category            Additional Category       Qty

1-Jan-17       A                        Add-1                           10

1-Jan-17       B                                                              7

2-Jan-17       A                        Add-1                            2

2-Jan-17       A                                                             15

3-Jan-17       C                                                              4

Table 2 combines Table 1 & converting Additional Category = "Add-1" entries to Category C

the formula is

UNION (

SELECTCOLUMNS( FILTER(Table1,Table1[Additional Category] = "Add-1"),

"Date",Table1[Date],

"Category",SUBSTITUTE(Table1[Category],"A","C"),

"Qty",Table1[Qty]))

Thus, the result I expected is

Category       Qty

A                    27 (10+2+15)

B                     7

C                    29 (10+2+4, which comes from Qty from Category = C & Additional Category = Add-1)

However, what I got is

Category       Qty

A                    27 (10+2+15)

B                     7

C                    4

It seems that the Qty from Additional Category = Add-1 where the Category has been changed from "A" to "C" is not included in the Category C.

Is there anything wrong in my formula?

Need advise. Thanks.

[v-jiascu-msft · Accepted solution]

Hi @RMV,

It seems your formula is incomplete. I created a new one. Would you like to try it?

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

Best Regards!

Dale

[RMV]

Hi @v-jiascu-msft, thanks for the help.