Create Summarize table with DAX

[Devesh]

Hello All,

I want to create a calculated table using 2 existing tables in Power BI.
1st table:

name	percentage	date
a	20%	01-10-2021
b	30%	01-10-2021
c	40%	01-10-2021
d	10%	01-10-2021

2nd table:

category	amount	date
one	500	01-10-2021
two	1000	01-10-2021
three	1500	01-10-2021

Output:

name	cat	amount	date
a	one	100	01-10-2021
b	one	150	01-10-2021
c	one	200	01-10-2021
d	one	50	01-10-2021
a	two	200	01-10-2021
b	two	300	01-10-2021
c	two	400	01-10-2021
d	two	100	01-10-2021
a	three	300	01-10-2021
b	three	450	01-10-2021
c	three	600	01-10-2021
d	three	150	01-10-2021

I want the "amount" from the 2nd table to be distributed using the "percentage" column in the 1st table. Please help. Thanks in advance.

[bcdobbs]

In the data you give everything has the same date.

In your real data are the two table related on date column?

[Devesh]

Yes, they are related on date column. As we add data for next month, it should be filtered by date column.

[Anonymous · Accepted solution]

Hi @Devesh,

I created a sample pbix file(see attachment), please check whether that is what you want. First, Please create the relationship for Table1 and Table2 base on date fields...

Create relationship base on date fields

1. If you want to get a summarized table, you can create a calculated table with following DAX formula:

Output = 
SUMMARIZECOLUMNS (
    'Table1'[name],
    'Table2'[category],
    'Table1'[date],
    "amount", SUM ( 'Table1'[percentage] ) * SUM ( 'Table2'[amount] )
)

2. If you just want to display as a table visual, you can simply create a measure as below. 

NAmount = SUM('Table1'[percentage])*SUM('Table2'[amount]) 

Best Regards