Create summarized tables with 2 dimensions from multiple tables

[velvetine_123]

Hi,

I'm looking to create a summarize table based on the following :

Table A :

Date	Category	Sales
1/1/2024	A	10
1/1/2024	B	15
2/1/2024	A	12

Table B :

Date	Category	Cost
1/1/2024	B	5
2/1/2024	C	4
2/1/2024	A	3

Dimension Table Date :

Date

1/1/2024

2/1/2024

3/1/2024

Dimension Table Category :

Category

A

B

C

The intended outcome based on the summarize table should look like this :

Table Summarized :

Date	Category	Sales	Cost
1/1/2024	A	10
1/1/2024	B	15	5
2/1/2024	A	12	3
2/1/2024	C		4

Appreciate any help !

[Samarth_18 · Accepted solution]

Hi @velvetine_123 ,

Please try below code:-

SummarizedTable =
VAR _uni =
    UNION (
        SELECTCOLUMNS (
            'Table A',
            "Date", 'Table A'[Date],
            "Category", 'Table A'[Category],
            "Sales", 'Table A'[Sales],
            "Cost", BLANK ()
        ),
        SELECTCOLUMNS (
            'Table B',
            "Date", 'Table B'[Date],
            "Category", 'Table B'[Category],
            "Sales", BLANK (),
            "Cost", 'Table B'[Cost]
        )
    )
RETURN
    SUMMARIZE (
        _uni,
        [Date],
        [Category],
        "Sales",
            SUMX (
                FILTER (
                    _uni,
                    [Date] = EARLIER ( [Date] )
                        && [Category] = EARLIER ( [Category] )
                ),
                [Sales]
            ),
        "Cost",
            SUMX (
                FILTER (
                    _uni,
                    [Date] = EARLIER ( [Date] )
                        && [Category] = EARLIER ( [Category] )
                ),
                [Cost]
            )
    )

[bhanu_gautam]

@velvetine_123 , Either you can use Join in Power Query to merge these tables by going to Home selecte merge query option or you can create summarized table by going to modelling view and selecting new table using below mentioned DAX

SummarizedTable =

SUMMARIZE(

    UNION(

        SELECTCOLUMNS(

            'Table A',

            "Date", 'Table A'[Date],

            "Category", 'Table A'[Category],

            "Sales", 'Table A'[Sales],

            "Cost", BLANK()

        ),

        SELECTCOLUMNS(

            'Table B',

            "Date", 'Table B'[Date],

            "Category", 'Table B'[Category],

            "Sales", BLANK(),

            "Cost", 'Table B'[Cost]

        )

    ),

    [Date], [Category],

    "Sales", SUMX(FILTER(UNION('Table A', 'Table B'), [Date] = EARLIER([Date]) && [Category] = EARLIER([Category])), [Sales]),

    "Cost", SUMX(FILTER(UNION('Table A', 'Table B'), [Date] = EARLIER([Date]) && [Category] = EARLIER([Category])), [Cost])

[velvetine_123]

Hey thanks for your response, but i keep getting error saying the number the number of columns in UNION are not the same. I've been trying to figure it out but I cant. Wondering if you know what is that about ?