many to many?

[ruthhacche]

I have the set up below. Transaction 01234 has an invoice for £2.00 and a Credit for £0.50 (net £1.50) This was settled by £1.00 paid on one date and £0.50 paid on another date. The customer table links to both via a customer ID in all 3 tables not shown.

For all transactions in my Order table I want to know how much has been allocated in the Allocations table. I have tried

Paid = CALCULATE ( sumx(ALLOCATIONS, ALLOCATIONS[Value]), CROSSFILTER ( ALLOCATIONS [ref]] ,ORDERS [Ref], Both) )

It is finding the correct value for this transaction line but it is losing the filter and crossing every order to every order to return millions of lines.

I am no doubt being thick. Please help.

 

[alansidman]

using Power Query three Mcodes that I believe are readable in BI. I don't have or use BI

Orders

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"REF", type text}, {"Value", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"REF"}, {{"ValueTotal", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

Allocations

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"REF", type text}, {"Value", Currency.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"REF"}, {{"AllocationsSum", each List.Sum([Value]), type number}})
in
    #"Grouped Rows"

Merged Queries.

let
    Source = Table.NestedJoin(Orders, {"REF"}, Allocations, {"REF"}, "Allocations", JoinKind.LeftOuter),
    #"Expanded Allocations" = Table.ExpandTableColumn(Source, "Allocations", {"AllocationsSum"}, {"Allocations.AllocationsSum"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Allocations",null,0,Replacer.ReplaceValue,{"Allocations.AllocationsSum"})
in
    #"Replaced Value"

Book10
	A	B	C
1	REF	ValueTotal	Allocations.AllocationsSum
2	01234	1.5	1.5
3	05678	3	0
Sheet5

 

[ruthhacche]

Yes. Grouping is the way forward. Thank you.

 

[alansidman]

You are welcome.