Allexcept using a virtual column

[charleshale]

I'm having some trouble with allexcept referencing a column on a virtual table and wondering why the virtual column fails whereas the same technique on the actual table works.    I'm curious whether there are any workarounds that could save me the step of having to add the column physically.  

Here is an example: Let's say I make a table that looks like this:

ResultTable = 

VAR _Step1 =

SUMMARIZE (Table1, Table1[ColumnA],

"@ColumnB", [measure])

VAR _Step2 = 

ADDCOLUMNS (_Step1, 

"@ColumnC",  SUMX (_Step1, [@ColumnB])

)

Return 

_Step2

So far so good.    But what if I want to add a column that aggregates @ColumnC by ColumnA?     I can easily do this via adding a column to the resulting ResultTable with something like "Aggregation = calculate ( sum ( @ColumnC ) , allexcept (ResultTable, ResultTable[ColumnA]).    But how would I do this in the virtual table?

Things I have tried that don't work due to failure to recognize the row context:

Var _Step3 = 

ADDCOLUMNS (_Step2,

"Aggregation1", calculate (  sum( @ColumnC ), allexcept (Table1, Table1[ColumnA]),

"Aggregation2",

       VAR _variable = Table1[ColumnA]
       Return 

       calculate (  sumx (_step1, @ColumnB ), _variable = Table1[ColumnA] )

Thank you for  any advice.    

[charleshale · Accepted solution]

@amitchandak, the great and ubiquitous, I've solved the issue and it's really interesting.   I'm not entirely sure why one works and the other fails but here's what works and what doesnt. 

VAR _Step3 = 
ADDCOLUMNS(_Step2,
"@aggregation-FAILS", 
    VAR _variable = Table1[ColumnA)
    RETURN
    Calculate (SUMX(_Step2, [@ColumnC]), _variable = Table1[ColumnA))
"@aggregation-WORKS", 
    VAR _variable = Table1[ColumnA)
    RETURN
    SUMX(
          FILTER (_Step2,  _variable = Table1[ColumnA),
          [@ColumnC])

I'm not smart enough yet DAX-wise to articulate why the first one fails when the 2nd one works, but it looks like a filter contexgt issue wherein the SUMX needs the filter inside the SUMX

[charleshale]

will prepare sample file ... in process

[amitchandak]

@charleshale ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Can you try something like this for last one

Var _Step3 =

ADDCOLUMNS (_Step2,

"Aggregation1", calculate ( sum( @ColumnC ), allexcept (_Step2, _Step2[ColumnA]),

"Aggregation2",

VAR _variable = maxX(_Step2,_Step2[ColumnA])
Return

calculate ( sumx (_step1, @ColumnB ), _variable = _Step2[ColumnA] )