Forum Discussion

velvetine_123's avatar
velvetine_123
Frequent Visitor
2 years ago
Solved

Create summarized tables with 2 dimensions from multiple tables

Hi,

 

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

Table A :

DateCategorySales
1/1/2024A10
1/1/2024B15
2/1/2024A12

 

 

Table B :

DateCategoryCost
1/1/2024B5
2/1/2024C4
2/1/2024A3

 

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 :

DateCategorySalesCost
1/1/2024A10 
1/1/2024B155
2/1/2024A123
2/1/2024C 4

 

Appreciate any help !

  • Samarth_18's avatar
    Samarth_18
    2 years ago

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

     

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    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's avatar
      velvetine_123
      Frequent Visitor
      2 years ago

      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 ?

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    2 years ago

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