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 : Date Category Sales 1/1/2024 A 10 1/1/2024 B 15 2/1/2024 A 12     Table B : Date...
  • 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]
            )
    )

     

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 ?