Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 alr...
Data Modelling
DAX
Need Help
New Table
power bi
  • FreemanZ's avatar
    FreemanZ
    3 years ago

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

     

FreemanZ's avatar
FreemanZ
Icon for Super User rankSuper User
3 years ago

hi Anonymous 

try to plot a matrix visual directly, like:

or?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

    • FreemanZ's avatar
      FreemanZ
      Icon for Super User rankSuper User
      3 years ago

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

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Thanks so much!