Relation column depends on condition (Power Pivot)

[bukimi]

Hello,
I always solved problems with VBA. Recently, I had 4-day training in Power BI and I start solving more with it instead (easier on customer side).

I have two files that need to interact with each other. One is supposed to be gathering sum of sales value per customer. Second table is "raw data" with all sales listed. The problem is, that first table lists customers by ID that is divided accross several columns in sales data (second table). Maybe it's better to show on example data (attached).

First table: Client ID (first column) is different by type. The type of ID is listed in second column (sorry, I can't install plugin to post in BB code, so I attach it as images)
Second table: "Raw data" that has all sales of all customers. No column is unique, but client is identified by first 3 columns (number, company, group). Raw data is stored in separate file.

Relation between tables depends on "ClientType" in first table. It should sum values either by column A, or column B or column C in raw data (table2)... Don't know how to get around this obstacle.
I did solve counting with nested (many) IF and SUMIFS formula, but it's both very slow and complicated to refresh data on client side. It would be much easier, if I could just replace raw data file on server and make Power BI read from it and sum values accordingly.

A formula solution to example. Real, final formula for real data is much longer (more types, more conditions):

=IF([@ClientType]="Number",SUMIFS(Tabela2[Sales Value],Tabela2[ClientNumber],[@ClientID]),IF([@ClientType]="Company",SUMIFS(Tabela2[Sales Value],Tabela2[ClientCompany],[@ClientID]),IF([@ClientType]="Group",SUMIFS(Tabela2[Sales Value],Tabela2[ClientGroup],[@ClientID]),0)))

Any tips how to proceed with getting data in table 1 by setting relation to Table 2?

 

[jdellasala]

For a Relationship to exist between two tables, one of them must have a column of unique values that the other table uses for one or more rows.
Your two tables are completely inconsistent. The table with ClientID had values used in two different columns in table with ClientNumber.
What you're looking to do is easy enough to do with a formula once the data makes sense.

Book1
	A	B	C	D
26	ClientNumber	ClientCompany	ClientGroup	SalesTotal
27	12	PartnerC	none	$ 115.00
28	34	none	Health care	$ 138.00
29	45	none	Agriculture	$ 305.00
30	67	none	none	$ 55.00
31	1109	none	Health care	$ 114.00
32	1245	none	none	$ 131.00
33	1279	CompanyA	none	$ 127.00
34	11200	none	none	$ 14.00
35	11890	none	none	$ 49.00
36	14550	PartnerC	none	$ 101.00
37	44568	none	Managers	$ 32.00
Sheet2

Cell Formulas
Range	Formula
D27:D37	D27	=SUMIFS(fRawData[SalesValue],fRawData[ClientNumber],[@ClientNumber])

Alternately, you could pull the two corrected tables into Power Query and get the same result merging the tables:

let
    Source = Excel.CurrentWorkbook(){[Name="dCustSalesPQ"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"ClientNumber", Int64.Type}, {"ClientCompany", type text}, {"ClientGroup", type text}}),
    MergedQueries = Table.NestedJoin(ChangedType, {"ClientNumber"}, fRawData, {"ClientNumber"}, "fRawData", JoinKind.LeftOuter),
    AggregatedfRawData = Table.AggregateTableColumn(MergedQueries, "fRawData", {{"SalesValue", List.Sum, "Sum of SalesValue"}}),
    ChangedType1 = Table.TransformColumnTypes(AggregatedfRawData,{{"Sum of SalesValue", Int64.Type}})
in
    ChangedType1

But first you have to clean up the data! Also, PLEASE use XL2BB to post data samples. It takes FOREVER to transcribe pictures!

 

[bukimi]

Sorry about the pictures. I had no way to install any add-in to my Excel on company computer. I also had to make example data.
Thank you for your help. I have no influence on the way that input (raw data) or output data (small Table1) look like, because I'm receiving input from server and transform it to a very specific needs for someone else... I know it's messy and inconsistent, that's why I needed help in the first place

I need SalesTotal calculated in the first messy table, not the second one. I mean, it has to remain "messy" with ClientID being either a number, or a type - determined by description in another column. Even if I make ClientNumber a unique column in raw data (add products values together), it's still pairing only with "Number" client type in first table, when I put a relation between tables.
It's like tables should be related by ClientNumber: when messy table asks for total of a number and related by Group: when messy table asks for total of a group (which consists of several client numbers). That's not easy and not something they teach on Power BI courses (sadly).

I also rewrote the same data onto another computer, to make it in XL2BB. I attach it now.

Zeszyt1
	A	B	C
1	ClientID	ClientType	SalesTotal
2	1245	Number
3	67	Number
4	11980	Number
5	44568	Number
6	CompanyA	Company
7	PartnerC	Company
8	Health care	Group
9	Agriculture	Group
10	Managers	Group
Arkusz1

Zeszyt1
	E	F	G	H	I
1	ClientNumber	ClientCompany	ClientGroup	ProductSold	Sales Value
2	12	PartnerC	none	Apples	€ 11,00
3	12	PartnerC	none	Pears	€ 19,00
4	12	PartnerC	none	Plums	€ 85,00
5	34	none	Health care	Pears	€ 94,00
6	34	none	Health care	Apples	€ 44,00
7	45	none	Agriculture	Apples	€ 88,00
8	45	none	Agriculture	Pears	€ 59,00
9	45	none	Agriculture	Plums	€ 75,00
10	45	none	Agriculture	Bananas	€ 83,00
11	67	none	Agriculture	Pears	€ 55,00
12	1109	none	none	Pears	€ 97,00
13	1109	none	Health care	Bananas	€ 17,00
14	1245	none	Health care	Plums	€ 81,00
15	1245	none	none	Bananas	€ 45,00
16	1245	none	none	Pears	€ 5,00
17	11200	none	none	Bananas	€ 14,00
18	11890	none	none	Apples	€ 49,00
19	12679	CompanyA	none	Pears	€ 97,00
20	12679	CompanyA	none	Plums	€ 30,00
21	14550	PartnerC	none	Plums	€ 77,00
22	14550	PartnerC	none	Bananas	€ 24,00
23	44568	none	Managers	Apples	€ 32,00
Arkusz1

 

[jdellasala]

I turned the second set of data into a Table named Sales, I found it harder to work with the output as a Table which I hardly ever do. I came up with this:

Book1
	A	B	C
1	ClientID	ClientType	SalesTotal
2	1245	Number	131
3	67	Number	55
4	11980	Number	NOT FOUND
5	44568	Number	32
6	CompanyA	Company	127
7	PartnerC	Company	216
8	Health care	Group	236
9	Agriculture	Group	360
10	Managers	Group	32
Sheet1

Cell Formulas
Range	Formula
C2:C10	C2	=IFS( NOT(ISERROR(MATCH(A2,Sales[ClientNumber],0))), SUMIFS(Sales[Sales Value],Sales[ClientNumber],A2), NOT(ISERROR(MATCH(A2,Sales[ClientCompany],0))), SUMIFS(Sales[Sales Value],Sales[ClientCompany],A2), NOT(ISERROR(MATCH(A2,Sales[ClientGroup],0))), SUMIFS(Sales[Sales Value],Sales[ClientGroup],A2), TRUE,"NOT FOUND")

I looked up functions to make sure it would work with 2019, so that should be good. Of note, the ISERR function didn't work and had to use IFERROR instead. Go figure! I broke up the formula with returns to help make the formula a bit clearer. And no, that doesn't use the Client Type column because using it wasn't showing when the ClientID wasn't found. It is a simpler formula though:

=IFS(
B2="Number", SUMIFS(Sales[Sales Value],Sales[ClientNumber],A2),
B2="Company", SUMIFS(Sales[Sales Value],Sales[ClientCompany],A2),
B2="Group",      SUMIFS(Sales[Sales Value],Sales[ClientGroup],A2),
TRUE,"NOT FOUND")

I left the TRUE statement in just in case there's no match for any of the three groups.
I've been there too, and sometimes you have to work with the junk you're given, but that's really a mess!
Hope that helps, and good luck!

 

[bukimi]

Thank you.
I got the formula part before. I actually posted a similar one in my first post. The problem with using formulas is that Excel works slowly when there are many of them - any changes to "messy table" makes it recalculate everything. It's also harder to manage when conditions change. Switching to manual calculation helps only a little. That's why I preferred Power Query solution, so refreshing pulls new data and recalculates it - it's easier to replace raw data with new data, too. (simple replacing source file in a folder does the trick)

I figured out something to try, sitting and thinking about it. I'll try it in on Monday.
1. Add both data sets by Power Query (they are usually separate files).
2. Transform raw data with Power Query, a few times, making different final table everytime:
Firstly, group sales by client number - it would finally have two columns only: client number and sales total. Let's call it "NumberTable".
Then, group sales by company - it would finally have two columns, too. Let's call it "CompanyTable".
3. Proceed similarly with all client types. Finally, join all tables together, one below another, making one big data table, where first column is unique key (it's either a number, a company, or a group). Of course making any "total sum" directly out of it would be a bad idea, since sales values may and will be duplicated among different keys.
4. First "messy table" then gets related one-to-one to joined data, pulling values by unique key. It doesn't even need to check contents of second column.

 

[jdellasala]

Then lets try Power Query. This is NOT efficient to set up, and I'm sure there are others that could do this a LOT more elegantly, but this does get the job done. I start with this:

Book1
	A	B	C	D	E	F	G	H
1	BlankReport			Sales
2	ClientID	ClientType		ClientNumber	ClientCompany	ClientGroup	ProductSold	Sales Value
3	1245	Number		12	PartnerC	none	Apples	11
4	67	Number		12	PartnerC	none	Pears	19
5	11980	Number		12	PartnerC	none	Plums	85
6	44568	Number		34	none	Health care	Pears	94
7	CompanyA	Company		34	none	Health care	Apples	44
8	PartnerC	Company		45	none	Agriculture	Apples	88
9	Health care	Group		45	none	Agriculture	Pears	59
10	Agriculture	Group		45	none	Agriculture	Plums	75
11	Managers	Group		45	none	Agriculture	Bananas	83
12				67	none	Agriculture	Pears	55
13				1109	none	none	Pears	97
14				1109	none	Health care	Bananas	17
15				1245	none	Health care	Plums	81
16				1245	none	none	Bananas	45
17				1245	none	none	Pears	5
18				11200	none	none	Bananas	14
19				11890	none	none	Apples	49
20				12679	CompanyA	none	Pears	97
21				12679	CompanyA	none	Plums	30
22				14550	PartnerC	none	Plums	77
23				14550	PartnerC	none	Bananas	24
24				44568	none	Managers	Apples	32
Sheet1

The names in Row 1 are the table names.
Step 1 is to pull in the Sales report table,

let
    Source = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"ClientNumber", Int64.Type}, {"ClientCompany", type text}, {"ClientGroup", type text}, {"ProductSold", type text}, {" Sales Value ", Int64.Type}})
in
    ChangedType

and then Reference it to three other reports named Number, Company, and Group. Each of those copies groups by the column indicated by its name to get the total sales for each of the items in that column.
Number is transformed like this:

let
    Source = Sales,
    GroupedRows = Table.Group(Source, {"ClientNumber"}, {{"Sales", each List.Sum([#" Sales Value "]), type nullable number}})
in
    GroupedRows

Company like this:

let
    Source = Sales,
    GroupedRows = Table.Group(Source, {"ClientCompany"}, {{"Sales", each List.Sum([#" Sales Value "]), type nullable number}})
in
    GroupedRows

and Group like this:

let
    Source = Sales,
    GroupedRows = Table.Group(Source, {"ClientGroup"}, {{"Sales", each List.Sum([#" Sales Value "]), type nullable number}})
in
    GroupedRows

These tables are the result, but I show them just for clarity:

Book1
	N	O	P	Q	R	S	T	U
1	Number			Company			Group
2	ClientNumber	Sales		ClientCompany	Sales		ClientGroup	Sales
3	12	115		PartnerC	216		none	553
4	34	138		none	838		Health care	236
5	45	305		CompanyA	127		Agriculture	360
6	67	55					Managers	32
7	1109	114
8	1245	131
9	11200	14
10	11890	49
11	12679	127
12	14550	101
13	44568	32
Sheet1

They can be loaded as a Connection Only. Now those three tables are merged with the blank report from above like this:

let
    Source = Excel.CurrentWorkbook(){[Name="BlankReport"]}[Content],
    MergedClientID = Table.NestedJoin(Source, {"ClientID"}, Number, {"ClientNumber"}, "Number", JoinKind.LeftOuter),
    ExpandedClientIdSales = Table.ExpandTableColumn(MergedClientID, "Number", {"Sales"}, {"ID Sales"}),
    MergedCompany = Table.NestedJoin(ExpandedClientIdSales, {"ClientID"}, Company, {"ClientCompany"}, "Company", JoinKind.LeftOuter),
    ExpandedCompanySales = Table.ExpandTableColumn(MergedCompany, "Company", {"Sales"}, {"Company Sales"}),
    MergedGroup = Table.NestedJoin(ExpandedCompanySales, {"ClientID"}, Group, {"ClientGroup"}, "Group", JoinKind.LeftOuter),
    ExpandedGroup = Table.ExpandTableColumn(MergedGroup, "Group", {"Sales"}, {"Group Sales"}),
    MergedSales = Table.CombineColumns(Table.TransformColumnTypes(ExpandedGroup, {{"ID Sales", type text}, {"Company Sales", type text}, {"Group Sales", type text}}, "en-US"),{"ID Sales", "Company Sales", "Group Sales"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Total Sales"),
    ChangedType = Table.TransformColumnTypes(MergedSales,{{"ClientID", type any}, {"ClientType", type text}, {"Total Sales", Int64.Type}})
in
    ChangedType

Which produces this report:

Book1
	J	K	L
1	SalesReport
2	ClientID	ClientType	Total Sales
3	1245	Number	131
4	67	Number	55
5	44568	Number	32
6	11980	Number
7	CompanyA	Company	127
8	PartnerC	Company	216
9	Health care	Group	236
10	Agriculture	Group	360
11	Managers	Group	32
Sheet1

It's worth noting that if the Sales table is updated, the Sales Report is updated by just going to the Data tab and selecting Refresh All. If the Sales data is coming in from an external source, it can be imported and the report updated by that same click.
Power Query is VERY powerful and worth learning. Hope that works for you.

 

[JGordon11]

Another way:

let
    Rpt = Excel.CurrentWorkbook(){[Name="ReportTemplate"]}[Content],
    Sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    tbl = Table.AddColumn(Rpt, "SalesTotal", each 
            List.Sum(Table.SelectRows(Sales, (x)=> Record.Field(x, "Client" & [ClientType]) = [ClientID])[Sales Value]))
in
    tbl

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ReportTemplate			Sales						Query Output
2
3	ClientID	ClientType		ClientNumber	ClientCompany	ClientGroup	ProductSold	Sales Value		ClientID	ClientType	SalesTotal
4	1245	Number		12	PartnerC	none	Apples	11		1245	Number	131
5	67	Number		12	PartnerC	none	Pears	19		67	Number	55
6	11890	Number		12	PartnerC	none	Plums	85		11890	Number	49
7	44568	Number		34	none	Health care	Pears	94		44568	Number	32
8	CompanyA	Company		34	none	Health care	Apples	44		CompanyA	Company	127
9	PartnerC	Company		45	none	Agriculture	Apples	88		PartnerC	Company	216
10	Health care	Group		45	none	Agriculture	Pears	59		Health care	Group	236
11	Agriculture	Group		45	none	Agriculture	Plums	75		Agriculture	Group	360
12	Managers	Group		45	none	Agriculture	Bananas	83		Managers	Group	32
13				67	none	Agriculture	Pears	55
14				1109	none	none	Pears	97
15				1109	none	Health care	Bananas	17
16				1245	none	Health care	Plums	81
17				1245	none	none	Bananas	45
18				1245	none	none	Pears	5
19				11200	none	none	Bananas	14
20				11890	none	none	Apples	49
21				12679	CompanyA	none	Pears	97
22				12679	CompanyA	none	Plums	30
23				14550	PartnerC	none	Plums	77
24				14550	PartnerC	none	Bananas	24
25				44568	none	Managers	Apples	32
26
Sheet2

 

[jdellasala]

Another way:

let
    Rpt = Excel.CurrentWorkbook(){[Name="ReportTemplate"]}[Content],
    Sales = Excel.CurrentWorkbook(){[Name="Sales"]}[Content],
    tbl = Table.AddColumn(Rpt, "SalesTotal", each
            List.Sum(Table.SelectRows(Sales, (x)=> Record.Field(x, "Client" & [ClientType]) = [ClientID])[Sales Value]))
in
    tbl

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ReportTemplate			Sales						Query Output
2
3	ClientID	ClientType		ClientNumber	ClientCompany	ClientGroup	ProductSold	Sales Value		ClientID	ClientType	SalesTotal
4	1245	Number		12	PartnerC	none	Apples	11		1245	Number	131
5	67	Number		12	PartnerC	none	Pears	19		67	Number	55
6	11890	Number		12	PartnerC	none	Plums	85		11890	Number	49
7	44568	Number		34	none	Health care	Pears	94		44568	Number	32
8	CompanyA	Company		34	none	Health care	Apples	44		CompanyA	Company	127
9	PartnerC	Company		45	none	Agriculture	Apples	88		PartnerC	Company	216
10	Health care	Group		45	none	Agriculture	Pears	59		Health care	Group	236
11	Agriculture	Group		45	none	Agriculture	Plums	75		Agriculture	Group	360
12	Managers	Group		45	none	Agriculture	Bananas	83		Managers	Group	32
13				67	none	Agriculture	Pears	55
14				1109	none	none	Pears	97
15				1109	none	Health care	Bananas	17
16				1245	none	Health care	Plums	81
17				1245	none	none	Bananas	45
18				1245	none	none	Pears	5
19				11200	none	none	Bananas	14
20				11890	none	none	Apples	49
21				12679	CompanyA	none	Pears	97
22				12679	CompanyA	none	Plums	30
23				14550	PartnerC	none	Plums	77
24				14550	PartnerC	none	Bananas	24
25				44568	none	Managers	Apples	32
26
Sheet2

KNEW there was a MUCH better way! Very cool.