Forum Discussion
Create Summarize table with DAX
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.
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
3 Replies
In the data you give everything has the same date.
In your real data are the two table related on date column?
Yes, they are related on date column. As we add data for next month, it should be filtered by date column.
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
