Forum Discussion
New table using values from two other tables
Hello,
I am working on analysing businesses' financial statements and need some help with writing DAX code to make a new table using values from another table, and names from another table. I already posted a similar question (New column in new table based on values of other tables ) but my problem has gotten a bit more complex so the solution there doesn't apply to this. Maybe Mahesh0016 has an answer for this as well? 😄
Say I have two tables, Table 1 and Table 2.
Table 1 (which has info on shop names, year, the original line item name from each shop's financial statements and the value corresponding to that line item)
| Shop name | Year | Line item name | Value |
| Shop A | 2022 | current assets total | 10 |
| Shop A | 2022 | non-current assets total | 20 |
| Shop A | 2022 | current liabilities | 30 |
| Shop B | 2022 | CA total | 40 |
| Shop B | 2022 | NCA total | 50 |
| Shop B | 2022 | CL total | 60 |
| Shop B | 2021 | CA total | 70 |
| Shop B | 2021 | NCA total | 80 |
| Shop B | 2021 | CL total | 90 |
Table 2 (which has the original line item name and a new name for consistency)
| Line item name | New name |
| current assets total | Total current assets |
| CA total | Total current assets |
| non-current assets total | Total non-current assets |
| NCA total | Total non-current assets |
| current liabilities total | Total current liabilities |
| CL total | Total current liabilities |
I want to use information from Tables 1 and 2 to ultimately get a table like this (let's call it Table 3):
Table 3
| Shop name | Year | Total current assets | Total non-current liabilities | Total current liabilities |
| Shop A | 2022 | 10 | 20 | 30 |
| Shop B | 2022 | 40 | 50 | 60 |
| Shop B | 2021 | 70 | 80 | 90 |
Many thanks in advance!
hi Anonymous
something like this?
Table3 = ADDCOLUMNS( SUMMARIZE( Table1, Table1[Shop Name], Table1[Year] ), "Total current assets", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total current assets" ), "Total non-current assets", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total non-current assets" ), "Total current liabilities", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total current liabilities" ) )
4 Replies
hi Anonymous
try to plot a matrix visual directly, like:
or?
Thanks, that's a good idea! But I would prefer to do it without having to plot a visual....is there a way to just use DAX to make the table?
Reason is I need this table to add to my data model. If I were to plot a matrix visual I'd have to save it as a csv and then load that into my model which I'd rather avoid doing.
hi Anonymous
something like this?
Table3 = ADDCOLUMNS( SUMMARIZE( Table1, Table1[Shop Name], Table1[Year] ), "Total current assets", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total current assets" ), "Total non-current assets", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total non-current assets" ), "Total current liabilities", CALCULATE( SUM(Table1[Value]), Table2[New Name]="Total current liabilities" ) )