Forum Discussion

hidenseek9's avatar
hidenseek9
Post Patron
7 years ago
Solved

How to append calculated column from one table with another table?

Hello Power BI Community,

 

I need a help with appending two tables together.

What I would like to do is to append a calculated column from one table to another.

Sample Power BI

 

In the attached BI, I have "Demand" table.

In it, I calculated gross sales as a calculated column as below screenshot.

(Demand cab)

 

In PL by SKU table, I have actual gross sales data as per below.

 

I would like to append these two tables including "demand cab" calculated column

and sort out by "SKU name Final", which is in "SAP Code Mapping" table.

The relationship is already created.

 

I would like to have a new table with SKU Name Final and gross sales information.

then I would like to put other calculated columns in the new table to calculate down to net sales.

 

If you can help with this, this is much appreciated.

Many thanks,

 

H

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi hidenseek9,

     

    I get it now. Try these table formulas, please.

    Table =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            Demand,
            "SKU Name Final", [SKU Name Final],
            "Date", [Date],
            "Sequence", [Sequence],
            "Gross Sales", [Demand Cab]
        ),
        SELECTCOLUMNS (
            'PL by SKU',
            "SKU Name Final", [SKU NAME],
            "Date", [Date],
            "Sequence", [Sequence],
            "Gross Sales", [GS - Gross Sales]
        )
    )
)

    Or,

    Table =
UNION (
    SELECTCOLUMNS (
        Demand,
        "SKU Name Final", [SKU Name Final],
        "Date", [Date],
        "Sequence", [Sequence],
        "Gross Sales", [Demand Cab]
    ),
    SELECTCOLUMNS (
        'PL by SKU',
        "SKU Name Final", [SKU NAME],
        "Date", [Date],
        "Sequence", [Sequence],
        "Gross Sales", [GS - Gross Sales]
    )
)

    How-to-append-calculated-column-from-one-table-with-another-table2

     

    Best Regards,
    Dale

4 Replies

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee
    7 years ago

    Hi hidenseek9,

     

    Is the NEW table in the snapshot what you'd like? Please don't share sensitive data due to it's public here.

    Table =
ADDCOLUMNS (
    SUMMARIZE ( 'SAP Code Mapping', 'SAP Code Mapping'[SKU Name Final] ),
    "Gross Sales", CALCULATE ( SUM ( 'PL by SKU'[GS - Gross Sales] ) ),
    "Demand cab", CALCULATE ( SUM ( Demand[Demand Cab] ) )
)

    How-to-append-calculated-column-from-one-table-with-another-table

     

    Best Regards,
    Dale

    • hidenseek9's avatar
      hidenseek9
      Post Patron
      7 years ago

      v-jiascu-msft

       

      Thank you for your quick response.

      What I would like to achieve is that,

      Final Outcome Columns

       

      Sorry I was not clear.

       

       

      Basically, "Demand cab" from Demand tab is gross sales and Gross Sales from PL by SKU tab is gross sales,

      so I would like to bring both of them in one column.

       

      Many thanks,

       

      H 

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        7 years ago

        Hi hidenseek9,

         

        I get it now. Try these table formulas, please.

        Table =
DISTINCT (
    UNION (
        SELECTCOLUMNS (
            Demand,
            "SKU Name Final", [SKU Name Final],
            "Date", [Date],
            "Sequence", [Sequence],
            "Gross Sales", [Demand Cab]
        ),
        SELECTCOLUMNS (
            'PL by SKU',
            "SKU Name Final", [SKU NAME],
            "Date", [Date],
            "Sequence", [Sequence],
            "Gross Sales", [GS - Gross Sales]
        )
    )
)

        Or,

        Table =
UNION (
    SELECTCOLUMNS (
        Demand,
        "SKU Name Final", [SKU Name Final],
        "Date", [Date],
        "Sequence", [Sequence],
        "Gross Sales", [Demand Cab]
    ),
    SELECTCOLUMNS (
        'PL by SKU',
        "SKU Name Final", [SKU NAME],
        "Date", [Date],
        "Sequence", [Sequence],
        "Gross Sales", [GS - Gross Sales]
    )
)

        How-to-append-calculated-column-from-one-table-with-another-table2

         

        Best Regards,
        Dale