Summarize three table fields in one table

Hi ask4atish 

You can append Table 2 and Table 3 in Power Query, but before you will need to rename both customer and vendor columns to  customer-vendor

Or use Union Dax Function

Hello ask4atish ,

You may use Power Query for getting the desired output:

• Merge Table 1 with Table 2 on Source no. and extract Customer Name

• Merge the result table above with Table 3 on Source no. and extract Vendor

• Comine the two columns table2.CustomerName and table3.Vendor

• Remove SourceNo column

You may find sample pbix file here

Cheers!
Vivek

If it helps, please mark it as a solution
Kudos would be a cherry on the top 🙂

https://www.vivran.in/

Connect on LinkedIn

As suggested by the other poster, you can append both Table2 and Table3 into one single "dimension" table and establish a 1:N relationship between the appended table and the "fact" table which has got the Amount

Then you can use RELATED() function with ISBLANK() to create a calculated column that will check if the source has a corresponding customer and if it doesn't it will pull up vendor. So we are looking at two RELATED() calls.

Keep in mind that you can do this operation without appending the tables and it would work just fine. But probably the real question would be how many rows are we looking at here in the "fact" table with "Amount" column. It might not be a good idea to create a calculated column there, especially with two RELATED() calls.

Hi,

Please try to create a calculated table as below:

Table =
UNION (
    SELECTCOLUMNS (
        'Table 2',
        "customer/vendor", 'Table 2'[customername],
        "Amount", RELATED ( 'Table 1'[amount] )
    ),
    SELECTCOLUMNS (
        'Table 3',
        "customer/vendor", 'Table 3'[vendor],
        "Amount", RELATED ( 'Table 1'[amount] )
    )
)

The result shows:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto