Calculate a column across multiple tables

[jjhammer]

Hello,

I have data across multiple tables that I need to summarize. Data links to the same customer name across all sources. It should not calculate for any customer with a blank or zero estimate value.

Whole calculation is something like:

Estimate - Open - Invoice - SNI = Adjustment

Example Data 1

Customer	Date	Estimate
Customer1	4/3/2021	1000
Customer2	4/3/2021
Customer3	4/3/2021	35000

Example Data 2

CustName	ItemID	Date	Open
Customer1	1234	3/30/2021	1000
Customer3	1235	4/1/2021	20000
Customer3	1236	4/3/2021	5000
Customer2	1233	4/1/2021	2000

Other tables are similar to Example Data 2 above.

[jjhammer]

I think the biggest issue is we recently had a data change. We've been including Adjustments instead of Estimates in calculations and it has worked fine. Data isn't quite as straightforward as my examples (shipped, open, SNI all have multiple lines per customer). If there isn't a simple way to do it, then it'll likely be easier aggregating the data outside of BI into the spreadsheet and importing the adjustment from there.

[edhans]

Maybe. If it is a one time thing, sure, do it outside of Power Query. But if it is something you will have to do repeatedly, like weekly, do it in Power Query. I'd rather spend 10hrs coding some Power Query transformations than have to do an hour of work every week with 25 manual transformation steps in a specific way to get a report to work.

No right answer here. It is a huge "It depends"

[edhans · Accepted solution]

You are going to have to give us more of what you are expecting @jjhammer but let me take a stab at this. You cannot just add columns like that across tables. You'd have to have this absurd DAX measure with a bunch of LOOKUPVALUE functions and that will not perform well at all.

Instead you should bring all of this into one table using a method like this:

1. Create a table in Power Query with unique customer numbers I've shown this in the code presented below.
2. Merge this [Main Table] with the first table and bring in the data.
3. Merge this [Main Table] with the second table and bring in the data
4. repeat for each table.

This is the result:

So there are some problems.

1. Customer 3 has 2 records in Table2, so that caused the 35,000 value in table 1 to get duplicated.
2. If you grouped table 2 by customer name first, summarizing the "Open" amount, that would work, but, it would remove both the itemID and Dates from that table, which are both different - 1235/1236, 4/1/2021, 4/3/2021.

The M code to do what I did, assuming your tables are called Table1 and Table2 is this:

let
    Source = List.Combine({Table1[Customer], Table2[CustName]}),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), {"Customer Name"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Customer Name", type text}}),
    #"Removed Duplicates" = Table.Distinct(#"Changed Type"),
    #"Merged Queries" = Table.NestedJoin(#"Removed Duplicates", {"Customer Name"}, Table1, {"Customer"}, "Table1", JoinKind.LeftOuter),
    #"Expanded Table1" = Table.ExpandTableColumn(#"Merged Queries", "Table1", {"Date", "Estimate"}, {"Date", "Estimate"}),
    #"Merged Queries1" = Table.NestedJoin(#"Expanded Table1", {"Customer Name"}, Table2, {"CustName"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries1", "Table2", {"ItemID", "Date", "Open"}, {"ItemID", "Date.1", "Open"})
in
    #"Expanded Table2"

But what really needs to happen is a discussion on how you want this model to work, and this is the challenge of Power BI. It is model driven, and getting the model just right will make your DAX easier, faster, and in some cases, just possible, as a poorely designed model simply won't work.

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.