Over coming Many-To-Many

[Comfy]

My Fact table consists of financial transactions.

Book1
	A	B
1	Account	Amount
2	1234	-60
3	1234	-78
4	456	-80
5	456	-196
6	987	651
7	987	18
8	987	68
Sheet1

Normally when grouping account codes you would use 1-To-Many

Book1
	J	K
1	Account	Heading
2	1234	Income
3	456	Income
4	987	Expenditure
Sheet1

But I would like to use one table to group account codes for multiple KPIs, an account code might be used in one or more KPIs

Book1
	E	F
1	Account	KPI
2	1234	KPI1
3	456	KPI1
4	1234	KPI2
5	456	KPI2
6	987	KPI2
7	456	KPI3
8	987	KPI3
Sheet1

Assuming the Measure was a simple sum the output would be:

Book1
	A	B
15	KPI	Amount
16	KPI1	-414
17	KPI2	323
18	KPI3	541
Sheet1

​

Can anyone assist?

 

[Eric W]

Maybe:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Account	Amount			Account	KPI			Account	Heading
2	1234	-60			1234	KPI1			1234	Income
3	1234	-78			456	KPI1			456	Income
4	456	-80			1234	KPI2			987	Expenditure
5	456	-196			456	KPI2
6	987	651			987	KPI2
7	987	18			456	KPI3
8	987	68			987	KPI3
9
10
11
12
13
14
15	KPI	Amount
16	KPI1	-414
17	KPI2	323
18	KPI3	461
Sheet1

Cell Formulas
Range	Formula
B16:B18	B16	=SUM(SUMIFS($B$2:$B$8,$A$2:$A$8,IF($F$2:$F$8=A16,$E$2:$E$8,"xx")))

 

[Comfy]

Hi Eric,

Thanks for taking the time to respond, I think I found an article that gives me the answer I'm searching for.

https://powerpivotpro.com/2012/11/a-mystifying-and-awesome-solution-for-many-2-many/