topic Create summarized tables with 2 dimensions from multiple tables in DAX Commands and Tips

Create summarized tables with 2 dimensions from multiple tables

velvetine_123 — Thu, 18 Jul 2024 11:28:35 GMT

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 :

Re: Create summarized tables with 2 dimensions from multiple tables

bhanu_gautam — Thu, 18 Jul 2024 11:47:16 GMT

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

Re: Create summarized tables with 2 dimensions from multiple tables

velvetine_123 — Fri, 19 Jul 2024 04:32:49 GMT

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 ?

Re: Create summarized tables with 2 dimensions from multiple tables

Samarth_18 — Fri, 19 Jul 2024 05:21:23 GMT

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