dax to join a table to an aggregate table

[Ffitzpatrick47]

This is for excel 2016, not really power bi, but let's face it.  Mrexcel is a ghost town for power pivot

anyway I have a table a

Table a

And a table that I need to aggregate table b

That I need to aggregate grouped on d2 (Table b aggregate):

Then I want to join table b aggregate with table 1 so I can get this pivottable

I bet there's a super complex nested groupby dax formula to do this, but I can't figure it out.  How would you even step your way to the full formula by testing each nest unless you use dax studio? 

[v-shex-msft]

Hi @Ffitzpatrick47,

I'm not sure how to achieve these with dax excel version, but these operation is possible to achieve in power bi.

Sample table formula:

b aggregate = 
SUMMARIZE (
    FILTER ( b, [d4] IN VALUES ( a[d3] ) ),
    [d2],
    [d4],
    "Percent", DIVIDE (
        CALCULATE ( SUM ( b[value] ), ALL ( b ), VALUES ( b[d2] ), VALUES ( b[d4] ) ),
        CALCULATE ( SUM ( b[value] ), ALL ( b ), VALUES ( b[d2] ) ),
        -1
    )
)

join Table = ADDCOLUMNS(a,"Percent",[measure]*LOOKUPVALUE('b aggregate'[Percent],'b aggregate'[d2],a[d2],'b aggregate'[d4],a[d3]))

Regards,

Xiaoxin Sheng

[Ffitzpatrick47]

There's a myth that excel 2016 handles variables, but I don't even have the simplest code to test it.  I can break up one of my successful calculate equations like

var f = filter(all(x[a],[a]=1)

calculate(sum(x[b]),f) but I don't see it working.  It just seems too good to be true