DAX: Nested sums over the same table

Question

I am trying to write a DAX measure in PowerBI that essentially is trying to do the following in sudocode:

Where A is the data table and a is a row element of A.

SUMX(A,
   SUMX(A,
      a_outer - a_inner
    )
)

My best attempts have been to use the EARLIER function though I understand this is mostly meant for calculated columns and have been unable to get this to work.

Any help would be appreciated

Thanks

Could you elaborate on what it is you're trying to do? Ideally a sample example of expected results. — Rory
Hi Rory I've added a pic that hopefully shows what the measure should be doing, so for each entry it takes the difference between it and all the other entries, sums them and then repeats for the other entries. The result will always be zero as it'll cancel out but when i split it out by the outer loop and expect to see that -6, -2, 2 6 pattern. Hope this helps, Cheers — James
ok, i can't really do the formal math jargon. But if we take the Cartesian product of a table and itself (A and A', where x is and element from A and y is and element from A'), every pair (x,y) will be partnered with a pair (y,x). So every two values in your table will follow the form (x-y)+(y-x) => x-x+y-y => 0. Is this really what you're trying to do? It's always zero. — Ryan B.
Right, its not quite what I want to do but gives the difficult part of the problem the function i'm doing is mroe complicated than just a difference — James

RADO RADO · Accepted Answer · 2020-01-02T20:48:57

This measure:

Test =
SUMX (
    MyTable,
    VAR Outer_A = MyTable[A] 
    RETURN 
    SUMX ( ALL ( MyTable[A] ), Outer_A - MyTable[A] )
)

gives this result:

MyTable[A] here refers to the column in your table that contains "a". The way it works: in the first loop, we store a in a variable. In the second loop, we need to iterate over all "a"s, so we use ALL function. During each iteration, we subtract current "a" from the "a" saved in the variable.

DAX: Nested sums over the same table

1 Answers